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.
Awesome!
Never know this can be done – I was about to write extra query and found this
Indeed very simple..
Thanks for sharing.
You are a god among men sir!
You rock! exactly what i was looking for
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!
Someone should buy you a beer for this. Short and sweet, just what I needed.
Thank you!
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
)
It worked for me.
Thanks for this tip!
great post! this made coding a bit easier. thanks for the tip.
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 )
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;
Forgot the “GROUP BY month” in the subselect clause…the joys of not having copy / paste!
Great! It was very helpful to me. Thanks a lot!
Наверно русский камент тут тоже не помешает: спасибо, чувак! Помогло!
P.S. про пиво в каментах чётко подметили
[google translate: Probably Russian kamenty then does not hurt: Thanks, dude! It helped!
P.S. of beer in kamentah clearly noticed]