MySQL Error: 1093. Can't Specify Target Table for Update in From Clause Workaround

Question

I have found a lot of examples and documentation on the workarounds for this but the standard example does not work for me.

In this answer a workaround is given in the form:

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

I used the same format but still get the same error. Here is my query.

UPDATE employees
SET salary = (SELECT salary FROM (SELECT * FROM employees WHERE employee_id= '100') AS t1)
WHERE employee_id='105';

The WHERE seems to break this for some reason, is there anything I am obviously doing wrong?

score 1 · Accepted Answer · answered Nov 17 '15 at 19:40

You can use an UPDATE query with a JOIN, like this:

UPDATE
  employees e CROSS JOIN (
    SELECT salary FROM employees WHERE employee_id='100'
  ) e1
SET
  e.salary = e1.salary
WHERE
  e.employee_id='105'

using a SELECT with an inner SELECT used to be a workaround, but the optimizer of newer mysql versions just ignores the outer select. As an alternative, you can tell the optimizer not to merge derived queries:

SET optimizer_switch = 'derived_merge=off'

MySQL Error: 1093. Can't Specify Target Table for Update in From Clause Workaround

1 Answers1