Update multiple rows into one query

Question

I need to update multiple rows and same column name with faster way. Till now i'm using this;

Begin Transaction;
Update Inventory set Quantity-=1 where ID=1;
Update Inventory set Quantity-=4 where ID=2;
Update Inventory set Quantity-=1 where ID=1;
Update Inventory set Quantity-=5 where ID=4;
Commit;

This method works well, but i dont if it fast to write all the query for each value in same table.Any suggestions? As i read sql server doesnt support duplicate key update...

Did you find this in your search stackoverflow.com/questions/1197733/… learn.microsoft.com/en-us/sql/t-sql/statements/… — P.Salmon
– P.Salmon, Commented Sep 21, 2018 at 11:46
Good point, @P.Salmon. MERGE could be applied very nicely. — Vladislav
– Vladislav, Commented Sep 21, 2018 at 11:47

Vladislav · Accepted Answer · 2018-09-21 11:34:33Z

1

For simple updates you could use a CASE statement:

UPDATE Inventory
SET Quantity += CASE ID
    WHEN 1 THEN -1
    WHEN 2 THEN -4
    WHEN 4 THEN -5
    ...
END
WHERE ID IN (1,2,4);

However, this would not be efficient for huge updates. In those cases I would prefer 'chunked' UPDATE grouped by ID values having the same update:

UPDATE Inventory
SET Quantity += a
END
WHERE ID IN (X...);

UPDATE Inventory
SET Quantity += b
END
WHERE ID IN (Y...);

...

A combination between the two is possible, also. Good luck.

answered Sep 21, 2018 at 11:34

Vladislav

3,0122 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dhi Over a year ago

But how can i be sure that all executions will succedd if i will not use transaction?@in second example

Vladislav Over a year ago

I didn't focus on transactions because you ask only about the updates. You can wrap both examples in a transaction. The transaction will ensure that everything within is being executed with appropriate ACID rules applied.

Collectives™ on Stack Overflow

Update multiple rows into one query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related