CODEWITHSUNDEEP
sql-server
Esty
Esty

Reputation: 1912

Update sequence in SQL Server

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 ?

Upvotes: 2

Views: 1680

Answers (1)

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

Steps of update statement execution is

  1. retrieval from table and variable assignment from left side
  2. 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

Upvotes: 1

Related Questions