Error 1093 states that you can't UPDATE or DELETE using a subquery if your subquery queries the table you are deleting from.
So you can't do
delete from table1 where id in (select something from table1 where condition) ;
Ok, what's the best way to work around that restriction, (assuming you really do need to subquery to perform the delete and cannot eliminate the self referencing subquery entirely?)
Edit:
Here's the query for those who are interested:
mysql> desc adjacencies ; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | parent | int(11) | NO | PRI | NULL | | | child | int(11) | NO | PRI | NULL | | | pathLen | int(11) | NO | | NULL | | +---------+---------+------+-----+---------+-------+ -- The query is going to -- tell all my children to -- stop thinking my old parents -- are still their parents delete from adjacencies where parent in ( -- ALL MY PARENTS,grandparents select parent from adjacencies where child=@me and parent!=@me ) -- only concerns the relations of my -- grandparents WHERE MY CHILDREN ARE CONCERNED and child in ( -- get all my children select child from adjacencies where parent=@me ) ;
So what I've tried so far is creating a temporary table called adjsToDelete
create temporary table adjsToRemove( parent int, child int ) ;
insert into adjsToRemove...
So now I have a collection of relations to delete, where the parent/child pairs each uniquely identify a row to delete. But how do I delete each pair from the adjacencies table now?
It seems I need to add a unique auto_increment
ed key to each entry in adjacencies
, is that right?
Currently, you cannot delete from a table and select from the same table in a subquery
- details
You just cannot cannot specify target table for delete
one of my workaround : MySQL DELETE FROM with subquery as condition