0

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?

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.

  • If an instance occurs when the uid exists in both tables, I want to use teh value from #check1. That was a good catch, I had not thought of that scenario. – user2676140 Aug 8 '16 at 12:25
  • Quick update to your syntax. The individual updates in the where clause should be [m] not the fully qualified name. The syntax as is presents an error. – user2676140 Aug 8 '16 at 12:27
0

You have a problem with your case. The syntax is

CASE 
    WHEN condition THEN value
    WHEN condition THEN value
    ...
    ELSE value
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.