Some days back while writing an update query I faced an error “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.
Other databases support this type of statement but MySQL requires a workaround.
Let me show you a simple example to give you the solution for this.For the example I have used Sakila database.
UPDATE film
SET film.language_id = 2
WHERE film.film_id IN (SELECT f.film_id
FROM film f
INNER JOIN film_actor fa
ON f.film_id = fa.film_id
WHERE fa.actor_id = 12);
I know this query can be written in other way but to demonstrate the error I have written the query in this way.
The above query will give you the MySQL target table error because we are trying to update the film table, but
the film table is also used to supply the list of IDs.
But the query will work if we write in this way
UPDATE film
SET film.language_id = 2
WHERE film.film_id IN (SELECT * FROM(SELECT f.film_id
FROM film f
INNER JOIN film_actor fa
ON f.film_id = fa.film_id
WHERE fa.actor_id = 12)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.
