CODEWITHSUNDEEP
sqlsql-update
Jimmery
Jimmery

Reputation: 10139

2 SQL Updates in 1 statement

Im trying to do something like the following:

UPDATE table SET fieldA='valueA' WHERE id='id1', SET fieldB='valueB' WHERE id='id2';

But cant seem to get it to work. Is there a way of doing this, or will I need to use two separate SQL commands?

Upvotes: 1

Views: 180

Answers (5)

uverture
uverture

Reputation: 1

UPDATE table SET fieldA='valueA',fieldB='valueB' WHERE id in ('id1','id2')

Upvotes: -3

onedaywhen
onedaywhen

Reputation: 57023

MERGE INTO YourTable
   USING ( VALUES ( 'id1', 'valueA', NULL ), 
                  ( 'id2', NULL, 'valueB' ) )              
      AS source ( id, fieldA , fieldB )
         ON YourTable.id = source.id
WHEN MATCHED THEN
   UPDATE 
      SET fieldA = COALESCE(source.fieldA, YourTable.fieldA), 
          fieldB = COALESCE(source.fieldB, YourTable.fieldB);

Upvotes: 1

Curtis
Curtis

Reputation: 103348

This is best done as 2 different UPDATE statements, because you have 2 different WHERE clauses:

UPDATE table SET fieldA='valueA' WHERE id='id1'
UPDATE table SET fieldB='valueB' WHERE id='id2'

Upvotes: 2

user330315
user330315

Reputation:

If you really need to do it in one statement, you can use this:

UPDATE table 
    SET fieldA = case when id = 'id1' then 'valueA' else fieldA end, 
        fieldB = case when id = 'id2' then 'valueB' else fieldB end
WHERE id in ('id1', 'id2');

But for clarity (and thus maintainability), it would be much better to use two statements.

Upvotes: 10

gadeynebram
gadeynebram

Reputation: 725

You'll need to use 2 separate SQL-statements because the set operator works on the whole resultset that's filtered by the where-clause.

UPDATE table SET fieldA='valueA' WHERE id='id1'; UPDATE table SET fieldB='valueB' WHERE id='id2';

Upvotes: 0

Related Questions