SQL update Query

Mansoor Mohammed 61 Reputation points
2021-12-16T00:28:08.58+00:00

157996-image.png

158071-image.png

SequenceNo is the PrimaryKey
TansactionSequence is auto increment for every Credit Card Number
Each Credit card can have muliple paymentVersion

I want SQL update statement where I pass the sequenceNo, in for loop (Ex. update table... where seqno=..)

Basically, I want to group by creditCardNumber and paymentVersion and check for the previous transactionsequenceNo(transactionsequenceNo -1) and update the Transaction and Comment column .

Expected out put
First occurace of E in transaction will be the same with comment as Error and the following transaction(group by creditCardNumber and paymentVersion) will change to N with the comment "Error in the Previous transaction"

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,258 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,036 Reputation points
    2021-12-16T07:57:22.987+00:00

    Hi,@Mansoor Mohammed
    Please check this:

    CREATE TABLE #TestTable    
    (  
         SequenceNo int,  
         Name varchar(20),  
         CreditCardNumber varchar(20),  
         TransactionSequence int,  
    	 PaymentVersion int,  
         Transactions varchar(10),  
         Comment varchar(100)  
     );  
          
     INSERT INTO #TestTable VALUES  
     (1, 'xxx', '111111111', 1,1, 'Y', ''),  
     (2, 'xxx', '111111111', 2,1, 'Y', ''),  
     (3, 'yyy', '222222222', 1,1, 'Y', ''),  
     (4, 'yyy', '222222222', 2,1, 'E', ''),  
     (5, 'yyy', '222222222', 3,1, 'Y', ''),  
     (6, 'zzz', '333333333', 1,1, 'Y', ''),  
     (7, 'zzz', '333333333', 2,1, 'Y', ''),  
     (8, 'bla', '444444444', 1,1, 'Y', ''),  
     (9, 'bla', '444444444', 2,1, 'E', ''),  
     (10, 'bla', '444444444', 3,1, 'Y', ''),  
     (11, 'bla', '444444444', 4,1, 'Y', ''),  
     (12, 'bla', '444444444', 5,2, 'Y', ''),  
     (13, 'bla', '444444444', 6,2, 'E', ''),  
     (14, 'bla', '444444444', 7,2, 'Y', ''),  
     (15, 'gla', '555555555', 1,1, 'Y', ''),  
     (16, 'gla', '555555555', 2,1, 'E', ''),  
     (17, 'gla', '555555555', 3,1, 'Y', ''),  
     (18, 'gla', '555555555', 4,2, 'Y', ''),  
     (19, 'gla', '555555555', 5,2, 'E', ''),  
     (20, 'gla', '555555555', 6,2, 'Y', '');  
      
      
    ;WITH CTE AS  
    (SELECT *,LAG (Transactions) OVER (PARTITION BY CreditCardNumber,PaymentVersion ORDER BY TransactionSequence) AS Lag        
     FROM #TestTable  
     WHERE CreditCardNumber IN (SELECT CreditCardNumber FROM #TestTable WHERE Transactions = 'E')  
           AND TransactionSequence>=2  
    )  
    UPDATE CTE   
     SET Transactions = CASE WHEN Transactions = 'Y' AND Lag IS NOT NULL THEN 'N' ELSE Transactions END,  
    	 Comment = CASE WHEN Transactions = 'E' THEN 'Error' ELSE Comment END;  
    UPDATE #TestTable   
    SET Comment ='Error in the Previous transaction' WHERE Transactions = 'N';  
      
    SELECT * FROM #TestTable  
    

    Output:
    158154-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Mansoor Mohammed 61 Reputation points
    2021-12-16T05:31:43.143+00:00

    sequenceNo with 12 and 18, starts with a new payment version


  2. Isabellaz-1451 3,611 Reputation points
    2021-12-16T08:36:36.913+00:00

    Hi @Mansoor Mohammed ,

    Please check .

    create table testtable  
    ([SequenceNo] integer ,  
     [Name] varchar(20),  
     [CreditCardNUM] VARCHAR(20),  
     [TransactionSequence] integer,  
     [Payment] integer,  
     [Transactions] varchar(10),  
     [Comment] varchar(max))  
      
     DELETE  FROM testtable  
     insert into testtable  
     select 1, 'xxx','111111111',1,1,'Y',''  
     UNION ALL  
     select 2, 'xxx','111111111',2,1,'Y',''  
     UNION ALL  
     select 3, 'yyy','222222222',1,1,'Y',''  
     UNION ALL  
     select 4, 'yyy','222222222',2,1,'E',''  
     UNION ALL  
     select 5, 'yyy','222222222',3,1,'Y',''  
      UNION ALL  
     select 6, 'zzz','333333333',1,1,'Y',''  
     UNION ALL  
     select 7, 'zzz','333333333',2,1,'Y',''  
      
      
    ;WITH CTE AS(SELECT MAX(TransactionSequence) AS MAXVAUE,Payment,Name FROM testtable T1 WHERE Transactions ='E' GROUP BY Name,Payment)  
    
    UPDATE T   SET Transactions ='N' FROM testtable T  INNER JOIN CTE C ON  C.NAME = T.NAME AND C.Payment = T.Payment  AND T.TransactionSequence > C.MAXVAUE   
    UPDATE testtable set Comment =   
     case when Transactions = 'N' THEN 'Error in the previous'  
    
     when Transactions = 'E' THEN 'Error'   
     ELSE ''  
     END  
    

    Best Regards,
    Isabella

    0 comments No comments