Update Query With If Exists 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?
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.