关于子查询：MySQL错误1093-无法在FROM子句中指定目标表进行更新

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

我的数据库中的表story_category条目已损坏。下一个查询返回损坏的条目：

1
2
3
4
5
6

SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);

我试图删除它们执行：

1
2
3
4
5

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category
INNER JOIN story_category ON category_id=category.id);

但是我得到了下一个错误：

#1093 - You can't specify target table 'story_category' for update in FROM clause

我该如何克服？

更新：此答案涵盖了常规错误分类。有关如何最好地处理OP的确切查询的更具体的答案，请参阅此问题的其他答案

在MySQL中，您不能修改在SELECT部分??中使用的同一表。
此行为记录在：
http://dev.mysql.com/doc/refman/5.6/en/update.html

也许您可以将桌子自己连接起来

如果逻辑足够简单以重新构造查询，则丢失子查询并使用适当的选择条件将表连接到自身。这将导致MySQL将表视为两种不同的事物，从而允许进行破坏性的更改。

1
2
3

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

或者，尝试将子查询更深地嵌套到from子句中。

如果您绝对需要子查询，则有一种解决方法，但这是
丑陋的原因包括性能：

1 2	UPDATE tbl SET col = ( SELECT ... FROM (SELECT.... FROM) AS x);

FROM子句中的嵌套子查询创建一个隐式临时
表，因此它不算作您要更新的表。

...但是要注意查询优化器

但是，请注意，从MySQL 5.7.6及更高版本开始，优化器可能会优化子查询，但仍然会给您错误。幸运的是，可以使用optimizer_switch变量关闭此行为。尽管除了短期修复或小型一次性任务外，我不建议这样做。

1	SET optimizer_switch = 'derived_merge=off';

感谢Peter V. M？rch在评论中提供的建议。

示例技术来自史瓦兹男爵(Baron Schwartz)，最初出版于Nabble，释义并扩展至此处。

NexusRex提供了一个很好的解决方案，用于从同一表中删除带有join的语句。

如果您这样做：

1
2
3
4
5

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
)

您将得到一个错误。

但是，如果再将条件包装起来，请选择：

1
2
3
4
5
6
7

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
) AS c
)

它会做正确的事！

说明：查询优化器对第一个查询执行派生合并优化(这会导致它失败并显示错误)，但是第二个查询不符合派生合并优化的条件。因此，优化器被迫首先执行子查询。

子查询中的inner join是不必要的。似乎您要删除category_id不在category表中的story_category中的条目。

做这个：

1
2
3
4

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category);

而不是：

1
2
3
4
5

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);

最近，我不得不更新同一表中的记录，如下所示：

1
2
3

UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;

1
2
3
4
5
6

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)

如果你做不到

1 2	UPDATE table SET a=value WHERE x IN (SELECT x FROM table WHERE condition);

因为它是同一张表，所以您可以欺骗并做到：

1 2	UPDATE table SET a=value WHERE x IN (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[更新或删除或其他]

这是我为在表中及其表中的WHERE子句中> = 1时将Priority列值更新为1而执行的操作，该列值使用同一表上的子查询来确保至少一行包含Priority = 1(因为这是执行更新时要检查的条件)：

1
2
3
4

UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);

我知道这有点丑陋，但效果确实不错。

最简单的方法是在子查询中引用父查询表时使用表别名。

范例：

1	insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

更改为：

1	insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

您可以将所需行的ID插入到临时表中，然后删除该表中找到的所有行。

这可能就是@Cheekysoft通过两步完成操作的意思。

根据@CheekySoft链接的Mysql UPDATE语法，它说在底部。

Currently, you cannot update a table and select from the same table in a subquery.

我猜您正在从store_category中删除，同时仍在联合中从中进行选择。

尝试这个

1
2
3
4

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;

如果某事不起作用，则通过前门进入后门：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;

update apples_new
set price = (select price from apples where variety = 'gala')
where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;

它很快。数据越大越好。

这个查询如何希望对您有帮助

1	DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL

尝试将Select语句的结果保存在单独的变量中，然后将其用于删除查询。

对于OP尝试实现的特定查询，执行此操作的理想且最有效的方法是根本不使用子查询。这是OP的两个查询的LEFT JOIN版本：

1
2
3

SELECT s.* FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;

DELETE s FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;