You Can't Specify Target Table For Update in from Clause

MySQL Error 1093 - Can't specify target table for update in FROM clause

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

In MySQL, you can't modify the same table which you use in the SELECT part.

This behaviour is documented at:
http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

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

Alternatively, try nesting the subquery deeper into a from clause ...

If you absolutely need the subquery, there's a workaround, but it's
ugly for several reasons, including performance:

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

The nested subquery in the FROM clause creates an implicit temporary
table, so it doesn't count as the same table you're updating.

... but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

You can't specify target table for update in FROM clause

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.

How to resolve MySQL error You can't specify target table X for update in FROM clause?

The reason why this doesn't work is that MySQL doesn't allow you to reference the table that you are updating (cancome) within a subquery.

This can however be overcome by using a query instead of the table itself in the FROM, which has the effect of copying the requested table values instead of referencing the one that you are updating.

So effectively this, even if counter intuitive, will work :

DELETE FROM cancome WHERE user_id IN
 ( SELECT user_id FROM (SELECT * FROM cancome) AS cancomesub
 GROUP BY user_id HAVING COUNT(user_id)>3 )
 limit 3

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

MySQL does not allow the syntax that you are trying to use. Instead, you can use the update/join syntax as follows:

update labsd.interns s
inner join (
    select i.id
    from labsd.grades g
    inner join labsd.interns i on i.id = g.id
    order by g.grade desc limit 10
) t on t.id = s.id
set s.accepted = 1

The subquery enumerates the ids of the 10 best candidates, then the outer query joins the results of the subquery with the original table, an sets the flags on matching rows.

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

Try this

DELETE FROM products
WHERE id = (SELECT * FROM
            (SELECT id 
            FROM products
            WHERE purchase_date LIKE '2019%'
            ORDER BY purchase_date DESC
            LIMIT 1)tblTmp);

The query is basically the same, except the inner select is wrapped inside another select.
The most important thing to note is that the original select has been given an alias “tblTmp“. (The name tblTmp is arbitrary, you can give it any alias name.) 
The alias is important because assigning one will tell MySQL to create a temporary table from this select query. 
The temporary table may then be used as the source criteria for the update statement.
 
The reason it is wrapped inside another query is because MySQL syntax does not let you assign an alias to a select query when it is part of an update statement. 
So we have to put it inside another query which, I suppose separates it from the update statement.

MySQL Error 1093 - Can't specify target table for update in FROM clause (two joins)

You could try with a join without subquery

update sales_order_parts as sop
INNER JOIN sales_orders as so on so.id = sop.order_id
  AND so.order_number = 209 
inner join master_part_list on sop.part_id = master_part_list.id
SET sop.unit_price = master_part_list.price * (1-(so.applied_discount/100))

How to resolve this mysql error #1093 - You can't specify target table 'person' for update in FROM clause

DELETE p1
FROM person p1
JOIN ( SELECT isFatherOf 
       FROM person 
       WHERE name= 'bob' ) p2 ON p1.name = p2.isFatherOf 

DELETE Statement -> "Multiple-Table Syntax"

---

Related Topics