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'

我怎么能克服这个？

1> Cheekysoft..：

---

更新:此答案涵盖一般错误分类.有关如何最好地处理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';

感谢PeterV.Mørch在评论中提出的建议.

示例技术来自最初在Nabble出版的 Baron Schwartz,在此进行了解释和扩展.

---

请注意,从[MySQL 5.7.6 on](http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html),优化器可以优化子查询然后仍然给你错误,除非你'SET optimizer_switch ='derived_merge = off';`:-(

---

@Cheekysoft,为什么不将值保存到变量中呢？

2> Ekonoval..：

---

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
)

它会做正确的事!!

说明:查询优化器对第一个查询执行派生合并优化(导致其因错误而失败),但第二个查询不符合派生合并优化的条件.因此,优化器必须首先执行子查询.

---

这个错误和解决方案没有逻辑意义......但它确实有效.有时候我想知道MySQL开发者使用的是什么药......

---

这很有效,谢谢!那么这里的逻辑是什么？如果它嵌套了一个级别,那么它将在外部部分之前执行？如果它没有嵌套,那么mySQL会在删除锁定表后尝试运行它吗？

---

也许是因为我今天陷入困境,但这是最简单的答案,即使它可能不是"最好的".

3> 小智..：

---

在inner join你的子查询是不必要的.它看起来像你想删除的条目story_category,其中category_id没有在category表中.

做这个:

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

而不是:

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

---

这应该是最好的答案!也许删除第一个"而不是".

4> Pratik Khadl..：

---

最近我不得不在同一个表中更新记录,如下所示:

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

---

这不能只写成`UPDATE技能SET type ='开发'WHERE type ='Programming';`？这似乎没有回答原始问题.

---

即使示例案例值得怀疑,该原则也是最好的理解和部署在现实世界的场景中.显示的查询有效,因为表列表中的隐式连接为子查询创建临时表,从而提供稳定的结果并避免检索和修改同一个表之间的冲突.

5> NexusRex..：

---

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
)

---

你能解释为什么这个有效,为什么只是嵌套一个级别的作品？这个问题已被问及对@ EkoNoval的问题的评论,但没有人回应.也许你可以帮忙.

6> Sequoya..：

---

如果你做不到

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

因为它是同一个表,你可以欺骗和做:

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

[更新或删除或其他]

7> sactiw..：

---

如果在表中使用> = 1并且在WHERE子句中使用同一个表上的子查询来更新优先级列值,则执行此操作以确保至少有一行包含Priority = 1(因为这是执行更新时要检查的条件):

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);

我知道它有点难看,但确实很好.

---

@Chris我知道,但有一个解决方法,这正是我试图用我的'UPDATE'查询显示,并相信我它的工作正常.我认为你根本没有真正尝试验证我的查询.

8> YonahW..：

---

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

这可能是@Cheekysoft分两步做的意思.

9> S.Roshanth..：

---

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

范例：

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

更改为：

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