How to Solve MySQL Error 1093: Can't Specify Target Table in FROM Clause?

MySQL Error 1093: Can't Specify Target Table in FROM Clause

When attempting to execute an update query on a table referenced in the FROM clause, you may encounter MySQL error 1093. This is because MySQL forbids modifications to tables directly involved in the selection process.

One alternative is to join the table to itself. By creating an alias for the table, you can select from it multiple times and modify the original table without the error:

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

Another solution is to nest the subquery deeper into the FROM clause:

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

This workaround creates an implicit temporary table from the subquery, allowing for an update without the error. However, this method may have performance implications.

It's important to note that in MySQL 5.7.6 and later, the optimizer may optimize out the subquery, resulting in the same error. If you encounter this, consider temporarily disabling the optimization:

SET optimizer_switch = 'derived_merge=off';

The above is the detailed content of How to Solve MySQL Error 1093: Can't Specify Target Table in FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!