Update multiple rows with different values in SQL

Question

I have a table like this :-

Product ID            Weight
A                     100
B                     100
C                     100
D                     100
E                     100 

I want to change it to:-

Product ID            Weight
A                     501
B                     601
C                     701
D                     801
E                     401 

How can I do that with SQL update command ??

Answer 1

Use Case expression like this

UPDATE products SET
Weight = 
CASE ProductID
   WHEN 'A' THEN 501
   WHEN 'B' THEN 601
   WHEN 'C' THEN 701
   WHEN 'D' THEN 801
   WHEN 'E' THEN 401
END
WHERE ProductID in ('A', 'B', 'C', 'D', 'E')

Without the WHERE clause, every row in the table would be tested (unnecessarily, since they will never match).

More info: CASE (Transact-SQL), Case Oracle, Case MySQL

Answer 2

UPDATE [TABLE] SET Weight = 501 WHERE [Product ID] = 'A'
GO
UPDATE [TABLE] SET Weight = 601 WHERE [Product ID] = 'B'
GO
UPDATE [TABLE] SET Weight = 701 WHERE [Product ID] = 'C'
GO
UPDATE [TABLE] SET Weight = 801 WHERE [Product ID] = 'D'
GO
UPDATE [TABLE] SET Weight = 401 WHERE [Product ID] = 'E'
GO