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 ??
Add a comment
|
2 Answers
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
7 Comments
Bob
Hi, I tried to do that with some extra :- 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 Store = 'HHH' and Company = 'ABC' The system prompted with the following error message:- Error: SQL0199 - Keyword WHERE not expected. Valid tokens: SET. (State:37000, Native Code: FFFFFF39)
gyromonotron
What RDBMS is used? (SQL SERVER, MySQL, Oracle, PostgreSQL)
gyromonotron
According to Error SQL0199 looks like you are using DB2
Bob
After i hv removed the brackets, it prompted with another error code:- Error SQL0407 - Null values not allowed in column or variable Weight
gyromonotron
Ok, table products contains some additional productIDs (not only A, B, C, D, E). So you should add ELSE statement for this case. (Example, CASE ProductID WHEN 'A' THEN 501 WHEN 'B' THEN 601 WHEN 'C' THEN 701 WHEN 'D' THEN 801 WHEN 'E' THEN 401 ELSE 100 END)
|
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
9 Comments
Bob
This is for single row but I hv multiple rows. How to do that ?
Bob
UPDATE [TABLE] SET Weight = 501 WHERE [Product ID] = 'A' UPDATE [TABLE] SET Weight = 601 WHERE [Product ID] = 'B' UPDATE [TABLE] SET Weight = 701 WHERE [Product ID] = 'C' UPDATE [TABLE] SET Weight = 801 WHERE [Product ID] = 'D' UPDATE [TABLE] SET Weight = 401 WHERE [Product ID] = 'E' I hv run one at a time. I cant execute them together ? Is there a way to run them concurrently.
Ralf de Kleine
They will never run concurrently, update performs one row at a time. Why is that important?
Bob
I hv thousand of rows to update. There must be a way to shorten the process. I cant be sitting here the whole day just to run row by row.
Ralf de Kleine
Thousand of rows shouldn't take more than seconds or minutes. There must be something else whats wrong. How much time does one update take? What does the Execution Plan look like?
|