SQL Update column value of row 1 from value of row 2

Question

Hi all We are using SQL 2005 (MS),

I have a table that stores transactions. each time a specific event occurs 3 rows are added to for a specific transaction to that table.

Now we want to change (update) a column in row 1 called commision which in row 1 is null with the value of the same column from row 2 which has a value say 123. is that possible?

Answer 1

update mytable a
   set commission = ( select commission
                        from mytable b
                       where a.transaction = b.transaction
                         and b.event = 'row 2 event' ) // some value that selects row 2
  where a.event = 'row 1 event'                        // some value that selects row 1
    and a.transaction = 'specific transaction'         // leave off for all transactions

From your comment I've reformatted the statement to be:

UPDATE PAYMENT a
SET COMMISSION= ( SELECT COMMISSION
                    FROM PAYMENT b
                   WHERE b.PAYMENT_SEQ = 3
                     AND a.transaction_ID = b.TRANSACTION_ID )
WHERE a.PAYMENT_SEQ = 1
  AND a.TRANSACTION_ID = 1234

Why use IN(1234) when =1234 is simpler and gets the same result. You can just match a.transaction_id = b.transaction_id in the inner select so you don't have to repeat your 1234 transaction selection on the inner select.