T-sql – How to update two records with two different values with two ‘where’ conditions at once

t-sqltransactionupdate

In T-SQL, how to have this:

DECLARE @amount decimal(18,2) = 1000
UPDATE account
    SET balance = balance +  @amount WHERE accountID= 1;
UPDATE account
    SET balance = balance -  @amount WHERE accountID= 2;

in one update query to make sure eg. no one is changing account currency between two updates? When a row with ID = 1 is locked I want the row with ID = 2 also to be locked. Isolation level is read commited. Something like:

   DECLARE @amount decimal(18,2) = 1000
   UPDATE account
        SET balance = balance +  @amount WHERE accountID= 1;
    AND
        SET balance = balance -  @amount WHERE accountID= 2;

Best Answer

UPDATE account
    SET balance = balance +  CASE WHEN accountID = 1 THEN amount 
                                               WHEN accountID = 2 THEN amount * -1
                                               ELSE 0
                                               END
WHERE accountID IN (1,2)

The * is the multiply operator, that means amount * -1 = - amount - dat-nguyen

Related Question