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?

Answers 2

  • 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 Questions