Reputation: 30734
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
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