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
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.
精彩评论