MySQL绕过错误1093 [英] MySQL Getting around error 1093

问题描述

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

所以你不能这样做

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

好的，解决该限制的最佳方法是什么，(假设您确实需要子查询来执行删除并且不能完全消除自引用子查询?)

以下是感兴趣的人的查询:

<上一页>mysql> desc 邻接；+---------+---------+------+-----+---------+-------+|领域 |类型 |空 |钥匙 |默认 |额外 |+---------+---------+------+-----+---------+-------+|家长 |整数(11) |否 |优先级 |空 |||孩子|整数(11) |否 |优先级 |空 |||路径长度 |整数(11) |否 ||空 ||+---------+---------+------+-----+---------+-------+-- 查询将——告诉我所有的孩子——别再想我的老父母了——仍然是他们的父母从邻接中删除父母在哪里(-- 我所有的父母，祖父母选择父母从邻接孩子=@我和父母！=@我)——只涉及我的关系-- 关心我孩子的祖父母和孩子在(——带上我所有的孩子选择孩子从邻接哪里父母=@我);

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

的临时表

创建临时表 adjsToRemove( parent int, child int ) ;插入 adjsToRemove...

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

看来我需要为 adjacencies 中的每个条目添加一个唯一的 auto_incremented 键，对吗?

解决方案

目前不能在子查询中从表中删除和从同一个表中选择- 详情

你只是不能不能指定要删除的目标表

我的一种解决方法:MySQL DELETE FROM with subquery as条件

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?

解决方案

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

这篇关于MySQL绕过错误1093的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！