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.
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.
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.
Fair enough. The program doesn't know which instance of "person" I'm referring to.
Nope, that's not the problem. The parser can figure out which `person` you're talking about. The problem is, MySQL just doesn't allow updates which directly or indirectly self-join.
select into another table all persons who meet your update predicate. Then update `person` from that other table.
posted by orthogonality at 6:08 PM on February 6, 2011 [1 favorite]
Nope, that's not the problem. The parser can figure out which `person` you're talking about. The problem is, MySQL just doesn't allow updates which directly or indirectly self-join.
select into another table all persons who meet your update predicate. Then update `person` from that other table.
posted by orthogonality at 6:08 PM on February 6, 2011 [1 favorite]
Incidentally, 5+ minute execution time in a project with a self-referential table makes me think maybe you're doing something like a recursive walk of a tree and querying the table a zillion times. MySQL does not make recursion easy for you, and you may want to re-think this as a nested sets problem. Cf. Managing hierarchical data in MySQL and maybe this stackoverflow question.
posted by Monsieur Caution at 8:51 PM on February 6, 2011
posted by Monsieur Caution at 8:51 PM on February 6, 2011
Why does this not work?
UPDATE person SET heir=NULL WHERE id = person.heir AND isdead=false
posted by Lukenlogs at 9:08 PM on February 6, 2011
UPDATE person SET heir=NULL WHERE id = person.heir AND isdead=false
posted by Lukenlogs at 9:08 PM on February 6, 2011
Lukenlogs -- I think your query would set heir to NULL for cases where the person is dead. I think the original query is meant to set HEIR to NULL when the heir is dead.
I think Blue Jello Elf has the right idea:
update person
set heir=NULL
where heir in (select ID from person where isdead=false)
This assumes "head" references the personID for the heir in the person table.
posted by nalyd at 4:43 AM on February 7, 2011
I think Blue Jello Elf has the right idea:
update person
set heir=NULL
where heir in (select ID from person where isdead=false)
This assumes "head" references the personID for the heir in the person table.
posted by nalyd at 4:43 AM on February 7, 2011
This thread is closed to new comments.
UPDATE a
SET heir = NULL
FROM Person a
LEFT OUTER JOIN Person b
ON a.heir = b.ID
WHERE [constraints]
posted by Blue Jello Elf at 5:08 PM on February 6, 2011