MySQL 错误 1093 - 无法在 FROM 子句中指定要更新的目标表

2022-01-23 00:00:00 subquery mysql sql-delete mysql-error-1093

我的数据库中有一个表 story_category 包含损坏的条目.下一个查询返回损坏的条目:

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:

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

我试图删除它们正在执行:

I tried to delete them executing:

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 - 您不能在 FROM 子句中指定目标表 'story_category' 进行更新

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

我该如何克服这个问题?

How can I overcome this?

推荐答案

更新:这个答案涵盖了一般错误分类.有关如何最好地处理 OP 的确切查询的更具体的答案，请参阅此问题的其他答案

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

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

也许您可以将表加入到自身中

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

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

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

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

如果您绝对需要子查询，有一个解决方法，但它是丑陋有几个原因，包括性能:

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

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

FROM 子句中的嵌套子查询创建了一个隐式临时表，因此它不算作您正在更新的同一张表.

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.

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

但是，请注意来自 MySQL5.7.6 及以后，优化器可能会优化子查询，但仍然会给您错误.幸运的是，optimizer_switch 变量可用于关闭此行为；尽管我不建议将此作为短期修复或一次性小任务.

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

感谢 Peter V. Mørch 在评论中提供此建议.

Thanks to Peter V. Mørch for this advice in the comments.

示例技术来自 Baron Schwartz，最初发表于 Nabble，在此进行了释义和扩展.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

相关文章