0

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...

Dhi
  • 157
  • 3
  • 11
  • 1
    Did you find this in your search https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 – P.Salmon Sep 21 '18 at 11:46
  • Good point, @P.Salmon. `MERGE` could be applied very nicely. – Vladislav Sep 21 '18 at 11:47
  • You update for ID=1 twice, is it on purpose? – Alex Sep 21 '18 at 11:48

1 Answers1

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.

Vladislav
  • 2,772
  • 1
  • 23
  • 42
  • But how can i be sure that all executions will succedd if i will not use transaction?@in second example – Dhi Sep 21 '18 at 11:36
  • 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. – Vladislav Sep 21 '18 at 11:45