Update syntax? Why does one work and the other not?
I'm primarily an Oracle developer/admin and I recently had the need to update a record in a table based on a different record in the same table, so I did this:
update tab1 set col1 = (select col1 from tab1 where key2 = somevalue)
where col1 = key1
The error I received was this:
You can't specify target table 'tab1' for update in FROM clause
Howver, if I do the same statement, but use a different table in the from clause (pretend it's a second table with the same structure) it works:
update tab1 set col1 = (select col1 from tab2 where key2 = somevalue)
where col1 = key1
update successful
Can anyone explain to me why the first doesn't work (and what an alternative would be), and why the second one does work?
Subject
Written By
Posted
Update syntax? Why does one work and the other not?
May 02, 2005 01:25PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.