CODEWITHSUNDEEP
sqldb2
ProgramNov
ProgramNov

Reputation: 801

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

Upvotes: 79

Views: 245180

Answers (12)

Aldebaran
Aldebaran

Reputation: 21

It can be useful to use an alias when joining tables to derive a value. Like this:

UPDATE tmp1 SET CODE = tmp2.CODE FROM tmpTABLE1 tmp1 INNER JOIN tmpTABLE2 tmp2 ON tmp1.ID1 = tmp2.ID2

Upvotes: 0

stevenb123
stevenb123

Reputation: 17

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

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

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

Upvotes: 1

pr0grammaniac
pr0grammaniac

Reputation: 11

No, you need to handle every statement separately..

UPDATE table1
 Statement1;
 UPDATE table 1
 Statement2;

And so on

Upvotes: 1

niktrs
niktrs

Reputation: 10066

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)

Upvotes: 87

Abdelghafour Ennahid
Abdelghafour Ennahid

Reputation: 441

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

Upvotes: 5

Kyle Undefined
Kyle Undefined

Reputation: 776

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

Upvotes: 7

PRacicot
PRacicot

Reputation: 106

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

Upvotes: 3

Ahmad
Ahmad

Reputation: 12737

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

Upvotes: 42

BradC
BradC

Reputation: 39946

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

Upvotes: 3

Limey
Limey

Reputation: 2772

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

Upvotes: 4

Brandon J. Boone
Brandon J. Boone

Reputation: 16472

NO!

You'll need to handle those individually

Update [table]
Set ID = 111111259
WHERE ID = 2555

Update [table]
Set ID = 111111261
WHERE ID = 2724

--...

Upvotes: 90

Related Questions