TSQL How to update column from row in same table?

Question

Hi,

Say I have this table schema.

ID AccNo Amount

Say I have this data

ID     AccNo    Amount
1      1020     100.00
2      2040     50.00

How do I write a TSQL update query to update AccNo 1020 amount column with the amount from 2040??

Malcolm

Answer 1

Use a nested select:

UPDATE tablename SET Amount = (
    SELECT Amount FROM tablename WHERE ID = 2 )
WHERE AccNo = 1200

Obviously if your condition is different, you'll have to tweak to suit, e.g. if AccNo is unique, you can use that.