MySQL can’t specify target table for update in FROM clause

If you’re at this page you probably have encountered an error similar to “Error Code: 1093 You can’t specify target table ‘my_table’ for update in FROM clause” The reason for this error is that MySQL doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria.  I don’t know why MySQL has this limitation, but my best guess is that it causes some issue due to file locking or perhaps could cause an infinite loop.  Other databases support this type of statement but MySQL requires a workaround.

Luckily there’s a solution. As an example below is a query that will set the priority to “1″ for all customers who have at least one matching purchase record. I realize this is a stupid way to run this particular query, however I am merely posting a simple example of a query to demonstrate the error and workaround. Jeez, get off my back!

A Non-Working Example:

UPDATE customer
SET customer.priority = 1
WHERE customer.id IN
(
  SELECT DISTINCT customer.id FROM customer INNER JOIN purchase ON customer.id = purchase.customer_id
)

The above query will give you the MySQL target table error because we are trying to update the customer table, but the customer table is also used to supply the list of IDs.

A Working Version of the Example Above:

UPDATE customer
SET customer.priority = 1
WHERE customer.id IN
(
  SELECT id FROM
  (
    SELECT DISTINCT customer.id FROM customer INNER JOIN purchase ON customer.id = purchase.customer_id
  ) AS tmptable
)

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 “tmptable“. (The name tmptable 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.

The information about creating the temporary table was borrowed from Xaprb’s blog, who goes into a little more detail about the mechanics.

26 Responses to “MySQL can’t specify target table for update in FROM clause”

  1. Burs June 27, 2011 at 7:35 pm #

    Awesome!

    Never know this can be done – I was about to write extra query and found this :)

    Indeed very simple..

    Thanks for sharing.

  2. David July 12, 2011 at 9:51 am #

    You are a god among men sir!

  3. Jerry July 26, 2011 at 11:29 am #

    You rock! exactly what i was looking for :D

  4. Jordan Stern September 29, 2011 at 11:29 am #

    I wish copy and pasting all my errors into Google generated helpful hits like this one!

    Top of Googles results and I’m sure glad it was. Thanks!

  5. Mauricio November 11, 2011 at 3:31 pm #

    Someone should buy you a beer for this. Short and sweet, just what I needed.

  6. David November 15, 2011 at 5:14 am #

    Thank you!

  7. Abhijit November 29, 2011 at 10:28 pm #

    Thanks a ton ! works like a charm dont forget to put the AS tmptable in the end
    and clsoe all open brackets :)

    UPDATE customer
    SET customer.priority = 1
    WHERE customer.id IN
    (
    SELECT id FROM
    (
    SELECT DISTINCT customer.id FROM customer INNER JOIN purchase ON customer.id = purchase.customer_id
    ) AS tmptable
    )

  8. Antonio Tajuelo December 8, 2011 at 9:55 am #

    It worked for me.

    Thanks for this tip!

  9. macarzer January 25, 2012 at 1:24 am #

    great post! this made coding a bit easier. thanks for the tip.

  10. Ben February 19, 2012 at 7:17 pm #

    How would you get around needing to specify a WHERE condition on the inner-inner subquery which uses data from the table being updated?

    By way of an example, say you have a sales table which holds data for all sales. You want to add a column to this table which holds the value of the difference between the sale in that row and the average sale in that month. (It’s a contrived example I know, bear with me.)

    In Oracle, I would have done something like this:
    UPDATE sales a
    SET a.diff_from_avg = a.sale_value – (SELECT avg(b.sale_value) FROM sales b WHERE a.sale_month = b.sale_month )

    • Ben February 19, 2012 at 7:41 pm #

      I actually found my own workaround for this. I’m a little surprised, but the following works:

      UPDATE sales
      JOIN ( SELECT AVG(sale_value) avg_sale_value, month
      FROM sales ) average_sale_value
      ON sales.month = average_sale_value.month
      SET sales.diff_from_avg = sales.value_value – avg_sale_value;

      • Ben February 19, 2012 at 7:43 pm #

        Forgot the “GROUP BY month” in the subselect clause…the joys of not having copy / paste!

  11. Luis March 9, 2012 at 7:08 am #

    Great! It was very helpful to me. Thanks a lot!

  12. Oleg March 30, 2012 at 6:02 am #

    Наверно русский камент тут тоже не помешает: спасибо, чувак! Помогло!
    P.S. про пиво в каментах чётко подметили ;)

    [google translate: Probably Russian kamenty then does not hurt: Thanks, dude! It helped!
    P.S. of beer in kamentah clearly noticed]

  13. Walms July 9, 2012 at 11:18 pm #

    Thanks, big help.

  14. Petrof July 15, 2012 at 6:30 am #

    I’m learning “SQL hard way” and it seams that first query is possible in sqlite but not in mysql. Thanks for this.

  15. aduranv October 10, 2012 at 9:22 am #

    Excellent!! Big help when I was making an update to migrate data to another database.

  16. Moyed Ansari October 31, 2012 at 2:32 am #

    It works for me like a charm :D

  17. Eddy2412 November 1, 2012 at 6:33 am #

    Nice dude!, i’ve been trying to find this answer in a lot of blogs, and you were the one who left me out of this trouble. Thanks a lot!, that was short and nice explanation

  18. Graetar November 2, 2012 at 8:38 am #

    Sir, you are a true hero of the internets :)

  19. Alexey January 16, 2013 at 1:54 am #

    Skill ‘knowledge how to use derived tables’ can make a Magic with your applications =)

  20. JuanCC March 22, 2013 at 3:43 pm #

    This ended a headache I had for a week, trying different solutions/workarround. I’m new to MySQL and was doing it all ising PHP foreach loops which overloaded the MySQL server and slowed down the whole aplication.I put in the same table both “bills” and “payments” (by installments and not) with a column named ‘type’ to differentiate them (bills type = 0, payments type = 1) and linked each payment to a specific bill thru a column named ‘transaction_id’.

    I had to update the bills ‘is_cleared’ values when the sum of the payments ‘amount’ is equal or superior to the bills ‘amount’ value.

    My code ended looking like this:
    UPDATE transactions
    SET is_cleared = 1
    WHERE transactions.id IN (SELECT id FROM (SELECT * FROM transactions AS bills WHERE bills.amount <=(SELECT SUM(amount) from transactions AS payments WHERE bills.id = payments.transaction_id) )AS tmptable);

    Thanks a lot. This got me lightyears forward.

    Juan

  21. Jem June 13, 2013 at 8:00 am #

    Thank you – this one had me in a pickle and your solution was simple and swift. Rock on.

  22. Міша November 22, 2013 at 4:27 pm #

    Mmm, why not avoid the subquery altogether? Something like this (untested):

    UPDATE customer, purchase
    SET customer.priority = 1
    WHERE customer.id=purchase.customer_id

    Would not that do the same thing without the hackish gymnastics?

    • Jason November 22, 2013 at 5:48 pm #

      I guess you didn’t read the part where I stated that this example query was intentionally simple – for demonstration purposes only. There are plenty of situations where a simple join wouldn’t work as an alternative.

Leave a Reply

Please leave these two fields as-is: