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 ??
views:
68answers:
2
+3
A:
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
More info: CASE (Transact-SQL), Case Oracle, Case MySQL
gyromonotron
2010-08-18 09:16:15
Hi, I tried to do that with some extra :-UPDATE products SETWeight =(CASE ProductID WHEN 'A' THEN 501 WHEN 'B' THEN 601 WHEN 'C' THEN 701 WHEN 'D' THEN 801 WHEN 'E' THEN 401END)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)
Bob
2010-08-18 09:36:07
What RDBMS is used? (SQL SERVER, MySQL, Oracle, PostgreSQL)
gyromonotron
2010-08-18 09:44:21
SQL SERVER......
Bob
2010-08-18 09:56:50
Try to remove round brackets...
gyromonotron
2010-08-18 10:01:12
According to Error SQL0199 looks like you are using DB2
gyromonotron
2010-08-18 10:02:57
After i hv removed the brackets, it prompted with another error code:-Error SQL0407 - Null values not allowed in column or variable Weight
Bob
2010-08-18 10:07:56
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 100END)
gyromonotron
2010-08-18 10:16:06
Thanks a lot. It works well. :-)
Bob
2010-08-18 10:49:03
It works well (for the ELSE) based on the assumption that the other productsIDs are 100. If I wish not to change to 100 but to maintain its origin value of the other productIDs, what should I do then ?
Bob
2010-08-19 01:10:40
You should leave old weight ( CASE ProductID WHEN 'A' THEN 501 WHEN 'B' THEN 601 WHEN 'C' THEN 701 WHEN 'D' THEN 801 WHEN 'E' THEN 401 ELSE Weight END)
gyromonotron
2010-08-19 08:11:35
A:
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
rdkleine
2010-08-18 09:16:20
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.
Bob
2010-08-18 09:56:01
They will never run concurrently, update performs one row at a time. Why is that important?
rdkleine
2010-08-18 10:02:25
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.
Bob
2010-08-18 10:05:59
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?
rdkleine
2010-08-18 10:08:31
What I meant earlier was after inserted the SQL statements :-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'I clicked Execute button. It prompted an error msg:-Error: SQL0199 - Keyword UPDATE not expected.Next, I ran the above statement one at a time ie.UPDATE [TABLE] SET Weight = 501 WHERE [Product ID] = 'A'clicked execute buttonUPDATE [TABLE] SET Weight = 601 WHERE [Product ID] = 'B'clicked execute buttonThat's what I meant, one at a time.
Bob
2010-08-18 10:18:30
Which version of SQL Server are you using? Do you use the management studio of SQL server?
rdkleine
2010-08-18 11:07:22
rdkleine
2010-08-19 07:19:31