Hi All

This sort of leads on from another post of mine:

http://p2p.wrox.com/topic.asp?TOPIC_ID=40233

... COuld anyone advise me on what the SQL syntax would be to update 1 row with another rows data within the same table. e.g..

UPDATE TABLE1 (COL1, COL2, COL3) WHERE COL1 = 7 With the data from TABLE1 (COL1, COL2, COL3) WHERE COL1 = 4

Many thanks!

Rit

__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER

Sorry let me just tweak my example:

UPDATE TABLE1 (COL2, COL3) WHERE COL1 = 7 With the data from TABLE1 (COL2, COL3) WHERE COL1 = 4

... removed COL1 from update.

... Does this look like good practice?..

UPDATE TABLE1
SET
    COL2 = (SELECT COL2 FROM TABLE1 WHERE (COL1 = 4)),
    COL3 = (SELECT COL3 FROM TABLE1 WHERE (COL1 = 4))

WHERE COL1 = 7

Yes, your last example code will work (you don't need the parinthesis around the where clause.

UPDATE TABLE1
SET
    COL2 = (SELECT COL2 FROM TABLE1 WHERE COL1 = 4),
    COL3 = (SELECT COL3 FROM TABLE1 WHERE COL1 = 4)

WHERE COL1 = 7