Update Multiple Rows With Different Values and a Single SQL Query

Sometimes you may need to update multiple rows of data in a table. This is no problem a lot of the time, as the fields to update may all need to be changed to the same value, in which case you might run a query like the following.

UPDATE mytable SET myfield = ‘value’ WHERE other_field = ‘other_value’;

But what about if you want to update multiple rows but you need the field set to a different value for each row? 

The syntax is as follows.

UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN ‘value’
WHEN 2 THEN ‘value’
WHEN 3 THEN ‘value’
END
WHERE id IN (1,2,3)

What about when you need to update multiple fields? This is easily done just by adding another CASE block.

UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN ‘New Title 1’
WHEN 2 THEN ‘New Title 2’
WHEN 3 THEN ‘New Title 3’
END
WHERE id IN (1,2,3)

— =============================
UPDATE TABLE2 SET

COL1=(SELECT CASE
WHEN T2.PERIOD <> T1.PostingPeriod THEN T1.TotalAmount
ELSE (-1)*(T1.TotalAmount) END),

COL2=(SELECT CASE
WHEN T2.PERIOD <> T1.PostingPeriod THEN (T1.TotalAmount+T2.TotalAmount1+T2.TotalAmount2)
ELSE ((-1)*(T1.TotalAmount)+T2.TotalAmount1+T2.TotalAmount2) END)

FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.ACCOUNTNUMBER = T2.ACCOUNTNUMBER WHERE T2.[YEAR]=@Year AND T2.PERIOD IN (0,T1.PostingPeriod)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s