MySQL Self-Referential Table Problem

February 6, 2011 5:02 PM

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.

posted by Electrius to computers & internet (5 answers total)

I mostly do MSSQL for this sort of thing, so I'm not sure if MySQL allows this, and you may have to futz with the syntax even if it does, but I usually do something like:

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

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]

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

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

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