T-Sql

如何一次使用兩個“where”條件更新具有兩個不同值的兩條記錄?

  • June 22, 2019

在 T-SQL 中,如何做到這一點:

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

在一個更新查詢中以確保例如。沒有人在兩次更新之間更改帳戶貨幣?當 ID = 1 的行被鎖定時,我希望 ID = 2 的行也被鎖定。隔離級別被讀取送出。就像是:

  DECLARE @amount decimal(18,2) = 1000
  UPDATE account
       SET balance = balance +  @amount WHERE accountID= 1;
   AND
       SET balance = balance -  @amount WHERE accountID= 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)

是乘法運算符,這*意味著amount * -1 = - amount- dat-nguyen

引用自:https://dba.stackexchange.com/questions/241072