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?

The answer by a1ex07 (delete with joins) will perform better on large tables than the workarounds using double-nested subquery. — Kip, Mar 31 '16 at 15:31

brunovianarezende · Answer 1 · 2016-06-01T14:35:44.870

8

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)

edited Jun 01 '16 at 14:35

answered Mar 21 '13 at 14:27

brunovianarezende

744
6
13

score 2 · Answer 2 · edited Mar 31 '16 at 15:27

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

edited Mar 31 '16 at 15:27

Kip

107,154
87
232
265

answered Dec 19 '10 at 20:18

a1ex07

36,826
12
90
103

You can use Inner Join on delete. – Pentium10 Dec 20 '10 at 09:02
So can someone clarify how this response can be used to perform the subquery listed in the question? – bobobobo Dec 20 '10 at 19:01
Untested, but something like `DELETE a1 FROM adjacencies AS a1 INNER JOIN adjacencies AS a2 ON a1.id=a2.id WHERE a1.parent=a2.parent and a2.child=@me and a2.parent!=@me;` – Pentium10 Dec 20 '10 at 22:04
@a1ex07 I've updated with an (untested) example of how this solves the original query – Kip Mar 31 '16 at 15:27

score 1 · Answer 3 · answered Mar 12 '13 at 12:02

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

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

edited May 23 '17 at 11:46

Community

1
1

answered Dec 20 '10 at 02:43

ajreal

46,720
11
89
119

So it looks like I will need an ID column in my adjacencies table like 'create table adjacencies( id int PRIMARY KEY, parent int, child int, pathLen int )` -- I cannot do this solution otherwise - right? – bobobobo Dec 20 '10 at 03:06
@bobobobo - Yes, you can create an extra column, and update it with the ID that matched,and delete if the extra column contains **NON-ZERO** value. **Or** create a snapshot table contains all the ID. I would prefer method two, as it did not touch on current working data. – ajreal Dec 20 '10 at 03:10

score 0 · Answer 5 · edited Sep 20 '17 at 18:43

0

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.

edited Sep 20 '17 at 18:43

marc_s

732,580
175
1,330
1,459

answered Sep 16 '17 at 02:28

Jalpesh

63
6

score 0 · Answer 6 · edited Dec 26 '22 at 22:18

0

DELETE FROM `table1` 
WHERE id IN (SELECT * 
             FROM 
                 (select max(`id`)FROM table1 group by Column2 having count(*) >1) x);

Here Column2 is a column on which you want to find duplicate records (it may have multiple columns).

This will delete only duplicate records.

edited Dec 26 '22 at 22:18

F. Müller

3,969
8
38
49

answered Dec 26 '22 at 12:16

Duraiswamy

1

MySQL Getting around error 1093

6 Answers6

Linked