[sql] 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

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

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

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

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

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

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.

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, you need to handle every statement separately..

UPDATE table1
 Statement1;
 UPDATE table 1
 Statement2;

And so on

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