MySQL 解决错误 1093

Question

错误 1093 指出，如果子查询查询要从中删除的表，则无法使用子查询进行 UPDATE 或 DELETE。

所以你不能这样做好

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

吧，解决该限制的最佳方法是什么（假设您确实需要子查询来执行删除并且无法完全消除自引用子查询？）

编辑：

这是感兴趣的人的查询：

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
)

;

所以到目前为止我所尝试的是创建一个名为 adjsToDelete 的临时表

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

所以现在我有一个要删除的关系集合，其中父/子对每个都唯一标识要删除的行。但是现在如何从邻接表中删除每个对呢？

看来我需要为邻接中的每个条目添加一个唯一的auto_increment键，对吗？

Answer 1

解决方法，可在 http://bugs.mysql.com/bug.php?id= 中找到6980，对我有用的是为将返回项目的子查询创建一个别名。所以

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

会改为

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

Answer 2

您可以执行

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

对于问题中的查询，这应该是等效的：

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 3

目前，您无法在子查询中从表中删除并从同一个表中进行选择
- 详细信息

你不能无法指定目标表对于删除

我的解决方法之一： MySQL DELETE FROM以子查询为条件

Answer 4

简化：

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

您可以毫不犹豫地使用这个。

您的查询：

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

更新的查询：

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

这里 Column2 是您要查找重复记录的列。

Answer 6

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

这里 Column2 是您要查找重复记录的列（它可能有多个列）。

这只会删除重复的记录。