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

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

Answer 3

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

Answer 4

You can use this one without hesitation.

Your query:

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

Updated query:

DELETE FROM table1 
WHERE id IN (SELECT * 
             FROM 
                 (SELECT MIN(id) FROM table1 GROUP BY Column2) x);

Here Column2 is column on which you want to find duplicate records.

Answer 5

Simplified:

-- Collect ids
CREATE TEMPORARY TABLE cleanup_lookup AS 
SELECT id FROM table1 WHERE condition;

-- Delete the selected records
DELETE t FROM table1 t INNER JOIN cleanup_lookup l ON t.id = l.id;

-- Temporary tables get dropped when the connection is closed.