Reputation: 707
Say I have a table where there are product IDs, product desc., and the language of each desc. I would like it so that if there was a description with a NULL value for American-English, it would update to the British-English version of that the description for that product. Is there a way to do this using the update command in SQL?
Upvotes: 0
Views: 98
Reputation: 6526
I normally prefer this syntax for updating values in one table from values in another (or in this case the same) table, b/c it is easy to change the UPDATE...SET
to a SELECT
for testing and to quickly see what values would be updated.
UPDATE p_US
SET p_US.product_desc = p_GB.product_desc
FROM dbo.product p_US
INNER JOIN dbo.product p_GB ON p_US.productID = p_GB.productID
WHERE p_US.language_code = 'US'
AND p_GB.language_code = 'GB'
AND p_US.product_desc IS NULL
;
and then you can swap out the first two lines above with this for quick testing to see what would be updated:
SELECT p_US.productID, p_US.product,
oldDesc = p_US.product_desc, newDesc = p_GB.product_desc
Upvotes: 1
Reputation: 10172
Maybe:
UPDATE my_table SET desc=(SELECT desc from my_table WHERE my_table.id=id AND my_table.lang='british') WHERE lang='american' and desc is NULL;
Upvotes: 0
Reputation: 56
update [table] set [column]= case [change factor] when '1' then 'X' else 'Y' end where [where clause]
Upvotes: 0