SQL Statement with multiple SETs and WHEREs

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 

11 Answers

NO!

You'll need to handle those individually

Update [table] Set ID = 111111259 WHERE ID = 2555 Update [table] Set ID = 111111261 WHERE ID = 2724 --... 

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) 

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 

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 

No, you would need to create a seperate query for each update.

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 

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 

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 

No, you need to handle every statement separately..

UPDATE table1 Statement1; UPDATE table 1 Statement2; 

And so on

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

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