CODEWITHSUNDEEP
sqldatabasesql-update
Mohammed Hisham
Mohammed Hisham

Reputation: 1

Update multiple rows with multiple condtions "each row with a different condition"

I want to make an update sql statement that could update multiple rows with multiple conditions each row depend on different condition like this one:

UPDATE tableName
SET row1_field1=newValue1, Row1_field2=newValue2 
WHERE row1_condition1=condition1
  AND SET row2_field1=newValue1, row2_field2=newValue2 WHERE row2_condition1=condition1
  AND SET row3_field1=newValue1, row3_field2=newValue2 WHERE row3_condition1=condition1

Upvotes: 0

Views: 106

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

You can't do this dynamically this easy. you have to put a hardcoded value in the <conditionRow..> in the WHEN clause:

UPDATE tableName
SET field1 = CASE WHEN condition = <conditionRow1_field1> THEN newValue1 
                  WHEN condition = <conditionRow2_field1> THEN newValue2 
                  ...
             ELSE field1 END,
    field1 = CASE WHEN condition = <conditionRow1_field2> THEN newValueRow1_field2 
                  WHEN condition = <conditionRow2_field1> THEN newValueRow2_field2 
                  ...
             ELSE field2 END

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try something like this

UPDATE tableName
SET row1_field1=case when row1_condition1=condition1 then newValue1 end, 
Row1_field2=case when row1_condition1=condition1 then newValue2 end,
row2_field1=case when row2_condition1=condition1 then newValue1 end , 
etc

Upvotes: 1

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

For that you should use CASE UPDATE . There are plenty of examples avail online.

eg:

update table set
    columnx = (case when condition then new_valuex else columnx end),
    columny = (case when condition then new_valuey else columny end)

Upvotes: 1

Related Questions