Share via

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.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. LiHong-MSFT 10,046 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

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  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,616 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