Update record based on the other record

user_17495827 0
Viewed 99

I have a table like this, I want to update the value of AttemptNumber column based on record with the value based on the previous record.

txnId UserId Retry AttemptNumber
1 12 False 0
2 12 True 1
3 12 True 2
4 12 False 0
5 12 True 1
6 12 True 2
7 12 False 0

Whenever I encounter Retry value as 'False', then I want to keep the AttemptNumber as 0.

Whenever I encounter Retry value as 'True', then I want to increment the value in the AttemptNumber. Currently I don't have the column AttemptNumber in the table, I will create the column and update the values based on the value present in 'Retry'.

tsql sql-server sql
2 Answers

You need to define groups, based on the values in the Retry column and number the rows appropriately:

Test data:

SELECT *
INTO Data
FROM (VALUES
   (1, 12, 'False', 0),
   (2, 12, 'True',  0),
   (3, 12, 'True',  0),
   (4, 12, 'False', 0),
   (5, 12, 'True',  0),
   (6, 12, 'True',  0),
   (7, 12, 'False', 0)
) v (TxnId, UserId, Retry, AttemptNumber)

Statement:

; WITH UpdateCTE AS (
   SELECT 
      TxnId, UserId, Retry, AttemptNumber,
      ROW_NUMBER() 
         OVER (PARTITION BY UserId, GroupId ORDER BY TxnId) - 1 AS NewAttemptNumber
   FROM (
      SELECT 
         *,
         SUM(CASE WHEN Retry = 'False' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY UserId ORDER BY TxnId) AS GroupId
      FROM Data
   ) t  
)
UPDATE UpdateCTE
SET AttemptNumber = NewAttemptNumber

Result:

TxnId UserId Retry AttemptNumber
1 12 False 0
2 12 True 1
3 12 True 2
4 12 False 0
5 12 True 1
6 12 True 2
7 12 False 0
user_65780800

you can use this code

declare @x int=0
update t1 set
[AttemptNumber]=iif(t1.Retry=1,@x,0),
@x=iif(t1.Retry=1,(@x + 1),0)
from t1
user_138842550
Related