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

Posted in :

2023-05-26

這個發生的情況會在 update / delete sql command, 這個 sub query 用在 select 時又是ok, 這個情況只有在 mysql 會遇到, 在 MSSQL 就合法,

解法:
https://stackoverflow.com/questions/37251621/how-to-resolve-mysql-error-you-cant-specify-target-table-x-for-update-in-from

或:
https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

mysql 解法, 是產生一個 sub sub query , 並把 sub usb query 多一個 as newTable 變成 sub query 的 table 就OK.

例如:

(
select your_field from (
select your_field from real_table where your_field=your_where
) as newTable
);

The problem is that MySQL, for whatever inane reason, doesn’t allow you to write queries like this:

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

That is, if you’re doing an UPDATE/INSERT/DELETE on a table, you can’t reference that table in an inner query (you can however reference a field from that outer table…)

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

This apparently causes the necessary fields to be implicitly copied into a temporary table, so it’s allowed.

I found this solution here. A note from that article:

You don’t want to just SELECT * FROM table in the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a good WHERE clause to limit the results, too.

mysql