SQL Server 中的更新顺序

创建脚本：

CREATE TABLE [dbo].[tblTEST]
(
    [AccountNO] [varchar](10) NOT NULL,
    [Serial] [int] NOT NULL,
    [AccountType] [varchar](1) NOT NULL,
    [Due] [money] NOT NULL,
    [Balance] [money] NOT NULL,
    [Flag] [bit] NOT NULL,

    CONSTRAINT [PK_tblTEST] 
    PRIMARY KEY CLUSTERED ([AccountNO] ASC, [Serial] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

现在我将按如下方式更新表格：[只是一个虚拟查询可能不会返回任何内容]

BEGIN TRAN

DECLARE @AccountNO VARCHAR(10), @AccountType VARCHAR(1), @Serial INT, @Balance AS MONEY, @PreBalance AS MONEY

UPDATE A
SET
        A.Balance = @Balance
        , @PreBalance = @Balance
        , @Balance = ( CASE WHEN @Balance IS NULL OR @AccountType <> A.AccountType
                            THEN A.Balance
                            ELSE @Balance - A.Due
                        END )
        , A.Flag = CASE WHEN @PreBalance = A.Balance THEN 0 ELSE 1 END
        , @AccountType = A.AccountType
FROM tblTEST A

SELECT * FROM tblTEST

ROLLBACK

我只想知道更新顺序它是否总是从最后开始有效，还是存在其他条件需要考虑？

update语句执行的步骤是

1. 从表中检索并从左侧赋值
2. 从左侧站点将数据设置为表

所以更新语句的执行顺序如下-

A.Balance = @Balance --4
, @PreBalance = @Balance --1
, @Balance = ( CASE WHEN @Balance IS NULL OR @AccountType <> A.AccountType
                    THEN A.Balance
                    ELSE @Balance - A.Due
                END ) --2
, A.Flag = CASE WHEN @PreBalance = A.Balance THEN 0 ELSE 1 END --5
, @AccountType = A.AccountType --3

example

AccountNO Serial AccountType Due       Balance   Flag 
--------- ------ ----------- --------- --------- ---- 
A1        1      1           1000.0000 2000.0000 0    
A1        2      1           1000.0000 2000.0000 0   

执行将是

第 1 行第 1 阶段

, @PreBalance = null --1
, @Balance = ( CASE WHEN null IS NULL OR null <> A.AccountType
                    THEN A.Balance
                    ELSE null - A.Due
                END )  --2
, @AccountType = A.AccountType --3

第 1 行第 2 阶段

A.Balance = 2000 --4
, A.Flag = CASE WHEN null = A.Balance THEN 0 ELSE 1 END --5

第 2 行第 1 阶段

, @PreBalance = 2000 --1
, @Balance = ( CASE WHEN 2000 IS NULL OR 1 <> A.AccountType
                    THEN A.Balance
                    ELSE 2000 - A.Due
                END ) --2
, @AccountType = A.AccountType --3

第 2 行第 2 阶段

A.Balance = 1000 --4
, A.Flag = CASE WHEN 2000 = 1000 THEN 0 ELSE 1 END --5

so on