Can u plz help me Query

Question

I want SQL update statement to(Ex. update table... where seqno=..)

1.Update the transaction colum in the table that has previous transaction(TansactionSequence minus one) from Y to E.
2.Update the colum to Comment2
3. Update previous commnet to Comment1

Ex:Update Transaction colum 5 and 10 to E and comment colum for colum4/5 and 9/10 to comment1/comment2.
Note: 1. TansactionSequence with 1 is always Y, and can have multiple Transaction Sequence numbers.
2. SequenceNo is a primary Key

Answer 1

This statement seems to work:

update T
set Transactions = 'E',
    Comment = case when Transactions = 'E' then 'Comment1' else 'Comment2' end
from 
(
    select *, 
        lag(Transactions) over (partition by CreditCardNumber order by TransactionSequence) as pt,
        lead(Transactions) over (partition by CreditCardNumber order by TransactionSequence) as nt 
    from MyTable
) T
where (Transactions = 'E' and nt = 'Y')
or (pt = 'E' and Transactions = 'Y')

Answer 2

Considering the same credit card number appears in the table multiply times, which are not in the consecutive order, the SequenceNo column should be used to make sure the next transaction Y is updated to E if the previous one is E:

DECLARE @MyTable TABLE (
    SequenceNo int,
    Name varchar(20),
    CreditCardNumber varchar(20),
    TransactionSequence int,
    Transactions varchar(10),
    Comment varchar(50)
);

INSERT INTO @MyTable VALUES
(1, 'aaa', '111111111', 1, 'Y', ''),
(2, 'aaa', '111111111', 2, 'Y', ''),
(3, 'bbb', '222222222', 1, 'Y', ''),
(4, 'bbb', '222222222', 2, 'E', ''),
(5, 'bbb', '222222222', 3, 'Y', ''),
(6, 'ccc', '333333333', 1, 'Y', ''),
(7, 'ccc', '333333333', 2, 'Y', ''),
(8, 'ddd', '444444444', 1, 'Y', ''),
(9, 'ddd', '444444444', 2, 'E', ''),
(10, 'ddd', '444444444', 3, 'Y', ''),
(11, 'bbb', '222222222', 1, 'Y', ''),
(12, 'bbb', '222222222', 2, 'Y', '');

WITH CTE AS (
    SELECT t.* 
    FROM @MyTable AS t
    WHERE t.Transactions = 'E' 
      OR (
        t.Transactions = 'Y' AND EXISTS (
            SELECT * 
            FROM @MyTable AS t1
            INNER JOIN (
                SELECT *
                FROM @MyTable
                WHERE Transactions = 'E' 
            ) AS e ON e.CreditCardNumber = t1.CreditCardNumber AND e.SequenceNo + 1 = t1.SequenceNo
            WHERE t1.SequenceNo = t.SequenceNo
        )
    )
)

UPDATE CTE 
SET Transactions = CASE WHEN Transactions = 'Y' THEN 'E' ELSE Transactions END,
Comment = CASE WHEN Transactions = 'Y' THEN 'Comment2' ELSE 'Comment1' END;

SELECT * FROM @MyTable;

Answer 3

For the changed details, try another statement:

;
with M as
(
    select CreditCardNumber, max(TransactionSequence) ms
    from MyTable
    where Transactions='E'
    group by CreditCardNumber
)
update T
set Transactions = 'E',
    Comment = case Transactions when 'E' then 'Comment1' else 'Comment2' end
from MyTable T 
inner join M on M.CreditCardNumber = T.CreditCardNumber and T.TransactionSequence >= M.ms

Answer 4

Hi，@Mansoor Mohammed
Please also check this:

DECLARE @TestTable TABLE   
(  
     SequenceNo int,  
     Name varchar(20),  
     CreditCardNumber varchar(20),  
     TransactionSequence int,  
     Transactions varchar(10),  
     Comment varchar(50)  
 );  
   
 INSERT INTO @TestTable VALUES  
 (1, 'xxx', '111111111', 1, 'Y', ''),  
 (2, 'xxx', '111111111', 2, 'Y', ''),  
 (3, 'yyy', '222222222', 1, 'Y', ''),  
 (4, 'yyy', '222222222', 2, 'E', ''),  
 (5, 'yyy', '222222222', 3, 'Y', ''),  
 (6, 'zzz', '333333333', 1, 'Y', ''),  
 (7, 'zzz', '333333333', 2, 'Y', ''),  
 (8, 'bla', '444444444', 1, 'Y', ''),  
 (9, 'bla', '444444444', 2, 'E', ''),  
 (10, 'bla', '444444444', 3, 'Y', ''),  
 (11, 'bla', '444444444', 4, 'Y', ''),  
 (12, 'bla', '444444444', 5, 'Y', '');  
  
;WITH CTE AS   
(  
 SELECT * FROM @TestTable  
 WHERE CreditCardNumber IN (SELECT CreditCardNumber FROM @TestTable WHERE Transactions = 'E')  
        AND TransactionSequence>=2  
)  
UPDATE CTE   
 SET Transactions = CASE WHEN Transactions = 'Y' THEN 'E' ELSE Transactions END,  
     Comment = CASE WHEN Transactions = 'Y'   
	                THEN 'Comment2' ELSE 'Comment1' END;  
  
SELECT * FROM @TestTable  

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.