Sql-server – Update Query With If Exists

sql serversql-server-2008-r2t-sql

I am trying to write a query that will update my table #master12.cg when it is null. So here are my steps needed:

1) Check if #master12.uid exists in #check1.dasboot and if it does, then update #master12.cg with that value
2) Check if #master12.uid exists in #check.uid and if it does update #master12.cg with the value in #check2.redbluegreen
3) If #master12.uid does not exist in either table then just leave #master12.uid as null

I have tried this query

Update #master12
Set [cg] = case 
            when exists (Select [dasboot] FROM [#check1] WHERE [#master].    [uid] = [#check1].[uid])
            when not exists (Select [redbluegreen] FROM [#check2] WHERE     [#master12].[uid] = [#check2].[uid]
       else [#master12].[cg]
       end
WHERE [#master12].[cg] IS NULL

However this query presents an error of:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.

How should I write this update statement so that the checks listed above are performed and my table is updated accordingly?

Best Answer

SQLServer case when statement doesn't work like that. You need a condition, and then a result. Your query only has a condition.

You could rewrite your query like that :

Update #master12
Set [cg] = [dasboot] 
from #master12 m
join [#check1] c1 on m.[uid] = c1.[uid]
WHERE m.[cg] IS NULL

Update #master12
Set [cg] = [redbluegreen] 
from #master12 m
join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

Or maybe in a single one like :

Update #master12
Set [cg] = isnull([dasboot], [redbluegreen])
from #master12 m
left join [#check1] c1 on m.[uid] = c1.[uid]
left join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

But it will also depend on what you want when you have a uid value present in both tables #check1 and #check2 and which value you want to be used for the update.

Related Question