MySQL Error 1093: You Can't Specify Target Table for Update

MySQL Error 1093 (HY000, ER_UPDATE_TABLE_USED) means an UPDATE reads from the same table that it is modifying in a subquery. The message says, You can't specify target table 'employees' for update in FROM clause. A similar restriction can affect some DELETE statements. See the MySQL 8.4 error reference and subquery restrictions.

Example: update a row using a subquery on the same table

Assume employees.employee_id is a primary key. This statement tries to copy the department ID from employee 42 to employee 10, but it reads employees in the subquery while the outer statement updates employees:

UPDATE employees
SET department_id = (
  SELECT department_id
  FROM employees
  WHERE employee_id = 42
)
WHERE employee_id = 10;

Use a self-join for row-to-row updates

For a simple copy from one row to another, use two aliases of the same table in a multiple-table UPDATE:

UPDATE employees AS target
JOIN employees AS source
  ON source.employee_id = 42
SET target.department_id = source.department_id
WHERE target.employee_id = 10;

The join reads the source row and updates the target row without a subquery. If employee 42 does not exist, the join matches no row and employee 10 is left unchanged; handle that case explicitly if it should produce a different result.

Materialize derived values before updating

When the update depends on an aggregate from the same table, use a derived table that MySQL must materialize. This example sets one employee’s salary to the average salary in that employee’s department:

UPDATE employees AS target
JOIN (
  SELECT department_id, AVG(salary) AS average_salary
  FROM employees
  GROUP BY department_id
) AS department_averages
  ON department_averages.department_id = target.department_id
SET target.salary = department_averages.average_salary
WHERE target.employee_id = 10;

The GROUP BY makes this derived table nonmergeable, so MySQL materializes its results before updating employees. MySQL can merge some derived tables into the outer query; if a rewrite still raises Error 1093, check whether the derived table is being merged. The optimizer supports materialization and NO_MERGE controls for derived tables; see derived table optimization and subquery optimization.

Do not add GROUP BY, LIMIT, or other clauses just to force materialization unless they preserve the rows and values your update is meant to use. For large or multi-step changes, a temporary table can make the intended snapshot explicit.

If your subquery reads a different table, this same-table restriction does not apply. See the MySQL UPDATE tutorial for ordinary subquery updates, or browse all MySQL error troubleshooting guides.