1

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;
2
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.