CODEWITHSUNDEEP
sql-server-2008
Marcus25
Marcus25

Reputation: 883

combine two update statements into one for different conditions

I need a quick way to write an update query

UPDATE Content
SET Status = 1
WHERE Id in (SELECT userId from [User] where Location = 'US')

In this query itself, I want to SET Status = 0 WHERE Id NOT IN(SELECT userId from [User]).

Basically, I want to combine two updates into one.

UPDATE Content
SET Status = 1
WHERE Id in (SELECT userId from [User] where Location = 'US')

AND

UPDATE Content
SET Status = 0
WHERE Id NOT in(SELECT userId from [User] where Location = 'US')

,Thanks

Upvotes: 1

Views: 413

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

Something like this should work:

update c
set Status = case when u.userId is not null then 1 else 0 end
from Content c
  left join [User] u on c.id = u.userId and u.Location = 'US'

For each Content row, we check if there is a corresponding US user and set Status accordingly.

Upvotes: 3

Related Questions