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.