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

MySQL Error 1093 - Can't specify target table for update in FROM clause

提问by Sergio del Amo

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

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

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 - You can't specify target table 'story_category' for update in FROM clause

#1093 - 您不能在 FROM 子句中指定更新的目标表“story_category”

How can I overcome this?

我怎样才能克服这个问题？

回答by Cheekysoft

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

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

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 中，您不能修改在 SELECT 部分中使用的同一个表。
此行为记录在：http: //dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

也许你可以把桌子加入到它自己

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.

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

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

Alternatively, try nesting the subquery deeper into a from clause ...

或者，尝试将子查询更深入地嵌套到 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);

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

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

... but watch out for the query optimiser

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

However, beware that from MySQL 5.7.6and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switchvariable 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.

但是，请注意，从MySQL 5.7.6开始，优化器可能会优化出子查询，但仍然会出现错误。幸运的是，该 optimizer_switch变量可用于关闭此行为；尽管我不建议将其作为短期修复或小型一次性任务进行。

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. M?rchfor this advice in the comments.

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

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

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

回答by Ekonoval

NexusRexprovided a very good solutionfor deleting with join from the same table.

NexusRex提供了一个很好的解决方案，用于从同一个表中删除连接。

If you do this:

如果你这样做：

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
)

you are going to get an error.

你会得到一个错误。

But if you wrap the condition in one more select:

但是，如果您再将条件包装在一个选择中：

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
)

it would do the right thing!!

它会做正确的事！！

Explanation:The query optimizer does a derived merge optimizationfor the first query (which causes it to fail with the error), but the second query doesn't qualify for the derived merge optimization. Hence the optimizer is forced to execute the subquery first.

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

回答by Ekonoval

The inner joinin your sub-query is unnecessary. It looks like you want to delete the entries in story_categorywhere the category_idis not in the categorytable.

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

Do this:

做这个：

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

Instead of that:

取而代之的是：

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

回答by Pratik Khadloya

Recently i had to update records in the same table i did it like below:

最近我不得不更新同一张表中的记录，我这样做了，如下所示：

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

回答by 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
)

回答by Sequoya

If you can't do

如果你做不到

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

because it is the same table, you can trick and do :

因为它是同一张桌子，你可以欺骗和做：

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

[update or delete or whatever]

[更新或删除或其他]

回答by sactiw

This is what I did for updating a Priority column value by 1 if it is >=1 in a table and in its WHERE clause using a subquery on same table to make sure that at least one row contains Priority=1 (because that was the condition to be checked while performing update) :

如果 Priority 列值在表中 >=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);

I know it's a bit ugly but it does works fine.

我知道这有点难看，但它确实很好用。

回答by S.Roshanth

The simplest way to do this is use a table alias when you are referring parent query table inside the sub query.

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

Example :

例子 ：

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

Change it to:

将其更改为：

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

回答by YonahW

You could insert the desired rows' ids into a temp table and then delete all the rows that are found in that table.

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

which may be what @Cheekysoft meant by doing it in two steps.

这可能是@Cheekysoft 分两步完成的意思。

回答by briankip

According to the Mysql UPDATE Syntaxlinked by @CheekySoft, it says right at the bottom.

根据@CheekySoft 链接的Mysql UPDATE 语法，它在底部说。

Currently, you cannot update a table and select from the same table in a subquery.

目前，您无法更新表并从子查询中的同一表中进行选择。

I guess you are deleting from store_category while still selecting from it in the union.

我猜你是从 store_category 中删除，同时仍然在联合中选择它。