Home I cant get this conditional update or insert statement to work in SQL Server
Reply: 1

I cant get this conditional update or insert statement to work in SQL Server

Bashir
1#
Bashir Published in 2017-11-13 14:00:53Z

This code is supposed to update a record if the name of the person in table 1 is the same as the name of the person in table 2 and if it is not then just insert the name and age of the person as a new record into table 1.

 SELECT
    CASE 
    WHEN
    [Name] = @Name 
    THEN
    UPDATE
    dbo.EmployeeInfo
    SET
    dbo.EmployeeInfo.Age = V.Age 
    FROM dbo.EmployeeInfo I
    INNER JOIN
    dbo.UpdateEmployeeInfo V
    ON
    V.[Name] = I.[Name]

    ELSE

    SELECT * INTO dbo.EmployeeInfo
    FROM dbo.UpdateEmployeeInfo
    WHERE [Name] = @Name

    END
    FROM dbo.EmployeeInfo

I have edited the code just to use IF conditions yet it still does not work

IF
dbo.EmployeeInfo.[Name] = @Name 
THEN
UPDATE
dbo.EmployeeInfo
SET
dbo.EmployeeInfo.Age = V.Age 
FROM dbo.EmployeeInfo I
INNER JOIN
dbo.UpdateEmployeeInfo V
ON
V.[Name] = I.[Name]

ELSE

SELECT * INTO dbo.EmployeeInfo
FROM dbo.UpdateEmployeeInfo
WHERE [Name] = @Name
TheGameiswar
2#
TheGameiswar Reply to 2017-11-13 14:07:29Z

update a record if the name of the person in table 1 is the same as the name of the person in table 2 and if it is not then just insert the name and age of the person as a new record into table 1.

You also could use merge

merge table1 as t
using table2 as s
on s.name=t.name
when matched then 
update 
set t.name=s.name
when not matched by target
then
insert (name,age)
values(age,name)

Merge has some issues as per this thread by Paul White..You could also use seperate statements like below in a transaction as per your need

update t
set t.name=s.name
from table1 t1
join
table2 s
on s.name=t.name

if @@rowcount=0
insert into table1
(name,age)
values
(10,'a')
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.340853 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO