MySQL Forums
Forum List  »  Newbie

Update syntax? Why does one work and the other not?
Posted by: Kelly Penhall-Wilson
Date: May 02, 2005 01:25PM

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?

Options: ReplyQuote


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.