MySQL Getting around error 1093

Question

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_incremented key to each entry in adjacencies, is that right?

Answer 1

A workaround, found in http://bugs.mysql.com/bug.php?id=6980, that worked for me is to create an alias to the sub query that will return the items. So

delete from table1 where id in 
  (select something from table1 where condition)

would be changed to

delete from table1 where id in
  (select p.id from (select something from table1 where condition) as p)

Answer 2

You can do

delete t1,t2 
FROM  table1 t1  
INNER JOIN 
table1 t2 ON (t2.something = t1.id);

For the query in the question, this should be equivalent:

delete A
from adjacencies A
join adjacencies B ON A.parent = B.parent AND B.child=@me AND B.parent != @me
join adjacencies C ON A.child = C.child AND C.parent=@me