CODEWITHSUNDEEP
sql
andreas
andreas

Reputation: 135

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

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?

Upvotes: 1

Views: 2620

Answers (1)

Paul Morgan
Paul Morgan

Reputation: 32518

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.

This syntax will work with other SQL versions but not SQL Server 2005 as LarsH has tested. The a alias will have to be removed from the statement.

Upvotes: 1

Related Questions