CODEWITHSUNDEEP
t-sql
sipsorcery
sipsorcery

Reputation: 30734

Update using case and select from different table

I'm trying to do an SQL update where I want to set the value of the column being updated depending on the value in a second table. The script below shows what I'm trying to do but so far I haven't hit on the correct syntax.

 update sometable set name = 
  case
   when (select newid from lookuptable where oldid = name) <> null then newid
   else name
  end

Upvotes: 1

Views: 923

Answers (1)

Bennor McCarthy
Bennor McCarthy

Reputation: 11675

UPDATE T
SET
  T.name = L.newid
FROM sometable T
INNER JOIN lookuptable L
  ON L.oldid = T.name

There's no need for a coalesce or an outer join, because you're only interested in updating the rows that match.

Also, when comparing a value to null, you should always use X IS NULL or X IS NOT NULL rather than X = NULL or X <> NULL, because equality (and inequality) operators always return false for null values.

Upvotes: 3

Related Questions