MySQL Workaround Error 1093

Question

Error 1093 states that you cannot UPDATE or DELETE using a subquery if your subquery requests a table that you delete.

So you can not do

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

Well, what’s the best way to get around this limitation (if you really need to execute the subquery in order to perform the deletion and cannot completely exclude the independent reference subquery?)

Edit:

Here's a request for those 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, so far I have been trying to create a temporary table called adjsToDelete

 create temporary table adjsToRemove( parent int, child int ) ; insert into adjsToRemove... 

So now I have a set of delete relationships, where the parent / child pairs uniquely identify the row to delete. But how can I remove every pair from the adjacency table?

It seems I need to add a unique auto_increment ed key for each entry at adjacencies , right?

Answer 1

Currently, you cannot delete from a table and select from the same table in a subquery - more

You just can't help but specify a target table to delete

one of my workarounds: MySQL DELETE FROM with subquery as condition

Answer 2

The workaround found at http://bugs.mysql.com/bug.php?id=6980 that worked for me is to create an alias for the helper request that will return the elements. So

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

will be changed to

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

Answer 3

You can do

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

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

 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

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. 

Answer 5

You can use this without hesitation.

Your request:

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

Updated request:

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

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