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

我的数据库中有一个表story_category，其中包含损坏的条目。下一个查询将返回损坏的条目：

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”

我如何克服这一问题？

当前回答

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

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

也许你可以把这张桌子连在一起

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

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

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

如果您确实需要子查询，有一个解决方法，但它是丑陋的原因有很多，包括性能：

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

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

…但要注意查询优化器

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

SET optimizer_switch = 'derived_merge=off';

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

示例技术来自于最初发表于Nabble的巴伦·施瓦茨（Baron Schwartz），此处对其进行了解释和扩展。

2008-09-05 10:07:48

其他回答

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
)

2011-12-23 20:03:08

如果从前门进来时有些东西不起作用，那么从后门走：

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;

速度很快。数据越大越好。

2009-03-29 02:26:30

NexusRex提供了一个非常好的解决方案，可以从同一个表中删除带有联接的内容。

如果您这样做：

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
)

你会得到一个错误。

但如果您将条件包装为一个以上的选择：

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
)

它会做正确的事情！！

解释：查询优化器对第一个查询进行派生合并优化（这会导致它失败并出现错误），但第二个查询不符合派生合并优化的条件。因此，优化器被迫首先执行子查询。

2012-03-23 17:23:55