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

2021-11-20 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);

我试图删除它们执行:

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.

相关文章