SQL Server 中的更新顺序 [英] Update sequence in 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]

现在我要更新表格如下:[只是一个虚拟查询可能什么都不返回]

Now I am going to update the table as following : [just a dummy query may be return nothing]

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

我只想知道更新顺序.它总是从最后开始起作用还是存在其他需要考虑的条件?

I just want to know the update sequence. Does it always works from the last or there exists other conditions to be considered ?

推荐答案

update语句执行的步骤是

Steps of update statement execution is

1. 从表中检索并从左侧进行变量赋值
2. 从左侧站点设置为数据到表格

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

so the sequence of execution of your update statement will be as follows-

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

示例

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

执行将

第一行第一阶段

, @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 阶段

row 1 phase 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 阶段

row 2 phase 2

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

等等

这篇关于SQL Server 中的更新顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！