CODEWITHSUNDEEP
sqlsql-servert-sql
Vamsi Simhadri
Vamsi Simhadri

Reputation: 322

Update record based on the other record

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'.

Upvotes: 2

Views: 344

Answers (2)

Arash Ghazi
Arash Ghazi

Reputation: 991

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

Upvotes: 2

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 3

Related Questions