mysqlYou can't specify target table 'emp' for update in FROM clause

这个错误是因为MySQL不支持在同一个查询中更新一个表并从同一个表选择数据。可能的解决方法是使用子查询或临时表来解决问题。您可以尝试以下两种方法：

方法一：使用临时表

CREATE TEMPORARY TABLE temp_table
SELECT * FROM emp WHERE id = 123;

UPDATE emp SET name = 'new name' WHERE id = 123;

DROP TEMPORARY TABLE temp_table;

方法二：使用子查询

UPDATE emp SET name = 'new name' WHERE id = 123 AND id NOT IN (SELECT id FROM (SELECT id FROM emp WHERE id = 123) as temp_table);

相关问题

[HY000][1093] You can t specify target table student for update in FROM clause

这个错误是由于MySQL的限制所导致的，当我们在一个UPDATE语句中需要更新的表同时在FROM子句中被引用时，就会出现这个错误。MySQL不允许在同一个语句中更新和查询同一个表。

为了解决这个问题，我们可以使用子查询来避免在同一个语句中同时更新和查询同一个表。具体来说，我们可以将需要更新的表放在子查询中，然后在外层查询中引用这个子查询的结果集来更新需要更新的数据。示例代码如下：

UPDATE student SET score = 90 WHERE id IN (SELECT id FROM (SELECT id FROM student WHERE score < 60) AS t);

在这个例子中，我们需要将学生成绩小于60分的记录的成绩更新为90分。由于我们需要更新的表student同时也在FROM子句中被引用，因此我们使用子查询将需要更新的记录的id取出来，然后在外层查询中更新这些记录的成绩为90分。

Error Code: 1093. You can't specify target table 'EMP' for update in FROM clause

Error Code: 1093 is a MySQL error that occurs when you try to update a table that is also used in the subquery of the update statement. Specifically, the error message "You can't specify target table 'EMP' for update in FROM clause" indicates that you are trying to update the 'EMP' table, but the same table is also referenced in the subquery.

To resolve this issue, you can use a workaround by creating a temporary table or using a nested subquery. Here's an example of how you can rewrite your query to avoid this error:

UPDATE EMP
SET column1 = value1
WHERE id IN (
    SELECT * FROM (
        SELECT id
        FROM EMP
        WHERE condition
    ) AS temp
);

In this example, we create a temporary table 'temp' using a nested subquery and then use it in the main update statement. This way, we avoid referencing the same table 'EMP' in the subquery.