SQL Statement with multiple SETs and WHEREs

Question

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

User · Answer

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

User · Answer

NO   You ll need to handle those individually  Update  table  Set ID   111111259 WHERE ID   2555  Update  table  Set ID   111111261 WHERE ID   2724  --

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

No  you need to handle every statement separately    UPDATE table1  Statement1   UPDATE table 1  Statement2    And so on

User · Answer

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

User · Answer

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

[sql] SQL Statement with multiple SETs and WHEREs

Examples related to sql

Examples related to db2