By bobobobo


2010-12-19 19:53:51 8 Comments

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 [email protected]
  and [email protected]
)

-- only concerns the relations of my
-- grandparents WHERE MY CHILDREN ARE CONCERNED
and child in
(
  -- get all my children
  select child
  from adjacencies
  where [email protected]
)

;

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?

5 comments

@Jalpesh 2017-09-16 02:28:52

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.

@brunovianarezende 2013-03-21 14:27:38

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)

@a1ex07 2010-12-19 20:18:47

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 [email protected] AND B.parent != @me
join adjacencies C ON A.child = C.child AND [email protected]

@bobobobo 2010-12-19 22:35:41

ahm, where's the subquery

@ajreal 2010-12-20 02:39:23

@alex07 - are you serious ?

@Pentium10 2010-12-20 09:02:54

You can use Inner Join on delete.

@bobobobo 2010-12-20 19:01:27

So can someone clarify how this response can be used to perform the subquery listed in the question?

@Pentium10 2010-12-20 22:04:31

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 [email protected] and [email protected];

@Kip 2016-03-31 15:27:54

@a1ex07 I've updated with an (untested) example of how this solves the original query

@reto 2013-03-12 12:02:59

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.

@ajreal 2010-12-20 02:43:23

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

@bobobobo 2010-12-20 03:06:32

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?

@ajreal 2010-12-20 03:10:22

@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.

Related Questions

Sponsored Content

17 Answered Questions

[SOLVED] MySQL error code: 1175 during UPDATE in MySQL Workbench

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

15 Answered Questions

[SOLVED] MySQL Error 1093 - Can't specify target table for update in FROM clause

16 Answered Questions

[SOLVED] How to get the sizes of the tables of a MySQL database?

  • 2012-03-08 15:30:31
  • JPashs
  • 632950 View
  • 844 Score
  • 16 Answer
  • Tags:   mysql

4 Answered Questions

[SOLVED] SQL injection that gets around mysql_real_escape_string()

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

Sponsored Content