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.

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

CodePudding user response：

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:

CodePudding user response：

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