Update sequence in SQL Server

Question

Create Script :

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 ?

This might be helpful: http://www.sqlservercentral.com/articles/T-SQL/68467/ — Felix Pamittan, Aug 13 '15 at 06:38
It's better to use CTE to calculate @Balance variable, then join UPDATE with the CTE — cyberj0g, Aug 13 '15 at 06:41
@cyberj0g u mean to use Recursive CTE update... I have used it but i want to know the flow of update statement. — Esty, Aug 13 '15 at 07:07

score 1 · Accepted Answer · answered Aug 13 '15 at 17:59

Steps of update statement execution is

retrieval from table and variable assignment from left side
set to data to table from the left site

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

example

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

execution will be

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

row 1 phase 2

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

row 2 phase 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

row 2 phase 2

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

so on

see [Quarky Update](http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx) and [Halloween Protection](https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/) — Esty, Aug 17 '15 at 10:48

Update sequence in SQL Server

1 Answers1

Linked