MySQL错误1093-无法在FROM子句中指定目标表进行更新 [英] MySQL Error 1093 - Can't specify target table for update in FROM clause

问题描述

我的数据库中的表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);

但是我收到下一个错误:

But I get the next error:

#1093-您无法在FROM子句中指定目标表"story_category"进行更新

#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

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.

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

但是，请注意 MySQL 5.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，

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

这篇关于MySQL错误1093-无法在FROM子句中指定目标表进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！