CODEWITHSUNDEEP
sql
ohhh
ohhh

Reputation: 707

How can I update the value of multiple rows to a different specified value in the same column?

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

Answers (3)

BateTech
BateTech

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

Emanuele Paolini
Emanuele Paolini

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

victorvictord
victorvictord

Reputation: 56

update [table] set [column]= case [change factor] when '1' then 'X' else 'Y' end where [where clause]

Upvotes: 0

Related Questions