SQL Statement with multiple SETs and WHEREs
SqlDb2Sql Problem Overview
I am wondering if this is a valid query:
UPDATE table
SET ID = 111111259
WHERE ID = 2555
AND SET ID = 111111261
WHERE ID = 2724
AND SET ID = 111111263
WHERE ID = 2021
AND SET ID = 111111264
WHERE ID = 2017
Sql Solutions
Solution 1 - Sql
NO!
You'll need to handle those individually
Update [table]
Set ID = 111111259
WHERE ID = 2555
Update [table]
Set ID = 111111261
WHERE ID = 2724
--...
Solution 2 - Sql
Best option is multiple updates.
Alternatively you can do the following but is NOT recommended:
UPDATE table
SET ID = CASE WHEN ID = 2555 THEN 111111259
WHEN ID = 2724 THEN 111111261
WHEN ID = 2021 THEN 111111263
WHEN ID = 2017 THEN 111111264
END
WHERE ID IN (2555,2724,2021,2017)
Solution 3 - Sql
No. That is not a valid query. You can only have one SET statement, with multiple fields, however, one WHERE clause as well
update table1 set field1=value1, field2=value2, field3=value3 where filed4=value5
Solution 4 - Sql
Nope, this is how you do it:
UPDATE table SET ID = 111111259 WHERE ID = 2555
UPDATE table SET ID = 111111261 WHERE ID = 2724
UPDATE table SET ID = 111111263 WHERE ID = 2021
UPDATE table SET ID = 111111264 WHERE ID = 2017
Solution 5 - Sql
No, you would need to create a seperate query for each update.
Solution 6 - Sql
You can also use case then like this:
UPDATE table
SET ID = case
when ID = 2555 then 111111259
when ID = 2724 then 111111261
when ID = 2021 then 111111263
when ID = 2017 then 111111264
else ID
end
Solution 7 - Sql
No. You'll have to do separate updates:
UPDATE table
SET ID = 111111259
WHERE ID = 2555
UPDATE table
SET ID = 111111261
WHERE ID = 2724
UPDATE table
SET ID = 111111263
WHERE ID = 2021
UPDATE table
SET ID = 111111264
WHERE ID = 2017
Solution 8 - Sql
since sql those all the lines you want it to do, I would do you're code like thise
Inside you Sql management too do execute query and this should work.
UPDATE table
SET ID = 111111259 WHERE ID = 2555
UPDATE table
SET ID = 111111261 WHERE ID = 2724
UPDATE table
SET ID = 111111263 WHERE ID = 2021
UPDATE table
SET ID = 111111264 WHERE ID = 2017
Solution 9 - Sql
No, you need to handle every statement separately..
UPDATE table1
Statement1;
UPDATE table 1
Statement2;
And so on
Solution 10 - Sql
You could do this
WITH V(A,B) AS (VALUES
(2555,111111259)
,(2724,111111261)
,(2021,111111263)
,(2017,111111264)
)
SELECT COUNT(*) FROM NEW TABLE (
UPDATE table
SET id = (SELECT B FROM V WHERE ID = A)
WHERE EXISTS (SELECT B FROM V WHERE ID = A)
)
Note, does not works on column organized tables. Use MERGE in that case
Solution 11 - Sql
Use a query terminator string and set this in the options of your SQL client application. I use ; as the query terminator.
Your SQL would look like this;
UPDATE table SET ID = 111111259 WHERE ID = 2555;
UPDATE table SET ID = 111111261 WHERE ID = 2724;
UPDATE table SET ID = 111111263 WHERE ID = 2021;
UPDATE table SET ID = 111111264 WHERE ID = 2017;
This will allow you to do a Ctrl + A and run all the lines at once.
The string terminator tells the SQL client that the update statement is finished and to go to the next line and process the next statement.
Hope that helps