MySQL Self-Referential Table Problem
February 6, 2011 5:02 PM Subscribe
Can't find any reference to this on Google. I'm writing a MySQL query for a self-referential table using an EXISTS clause.
posted by Electrius to computers & internet (5 answers total)
The actual query is: UPDATE person SET heir=NULL WHERE NOT EXISTS (SELECT 1 FROM PERSON WHERE id=person.heir AND isdead=false);
This generates ERROR 1093 (HY000): You can't specify target table 'person' for update in FROM clause
Fair enough. The program doesn't know which instance of "person" I'm referring to. Is there a way to specify this, or a workaround? Obviously it's possible to write a PHP loop that does basically the same thing, but the script that this line is a tiny part of already takes 5+ minutes to execute and I would be loathe to go for such an inelegant solution. I have a pretty low level of understanding of MySQL so if there's something obvious, please let me know.