CODEWITHSUNDEEP
mysqlsql
DumbQuesionGuy314
DumbQuesionGuy314

Reputation: 173

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

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?

Upvotes: 0

Views: 329

Answers (1)

fthiella
fthiella

Reputation: 49079

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'

Upvotes: 1

Related Questions