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.
If you are a developer or DB administrator it’s a common situation that you need to update MySQL table , for example to set user statuses depending on Id column.
Let’s say that we need to unvalidate users with Id < 100. If you try to run the following query:
update Users set Valid = 0
where Id in (
select Id from Users where Id < 100
)You’ll get the error message:
#1093 — You can’t specify target table ‘xxx’ for update in FROM clause
Explanation
MySQL doesn’t allow updating the table you are already using in an inner select as the update criteria.
Many other database engines has support for this feature, but MySQL doesn’t and you need to workaround the limitation.
Solution
So, how to update the same table in MySQL?
The trick is to use a subquery, i.e. to update a table while selecting from it itself in a subquery.
update Users set Valid = 0
where Id in (
select Id from (
select Id from Users where Id < 100
) as t
)This is basically the same question as above, except the inner select is wrapped inside another select.
The alias in this query is important because it tells MySQL to create a temporary table from this select query and it may be used as the source criteria for the update statement.
Consider that you shouldn’t use select * from table in the subquery, but rather particular field due to perfrormance issues.
Получил такую ошибку при выполнении SQL запроса со вложенным подзапросом следующего вида.
UPDATE table_name
SET col = (SELECT col FROM table WHERE id = :x)
WHERE id = :yПричина ошибки довольно проста, MySQL не даст обновить ту же самую таблицу, из которой происходит чтение. Проблему можно решить парой способов.
Способ 1. C использованием JOIN, предпочтительный.
UPDATE table_name t1
JOIN table_name t2 ON t2.id = :x
SET t1.val = t2.val
WHERE t1.id = :yСпособ 2. С использованием SELECT FROM SELECT. В этом случае для результата внутреннего подзапроса будет создана временная таблица, и обновление пройдет успешно.
UPDATE table_name
SET col = (SELECT col FROM (SELECT col FROM table WHERE id = :x) AS t2)
WHERE id = :yПримечание. Если с SELECT FROM SELECT всё понятно, то многие не знают что JOIN также работает при запросах с оператором DELETE. Возьмём к примеру таблицу (id, name), где id — уникальный идентификатор, name — название, и возникла задача удалить записи с дубликатами названий. Решить проблему можно таким вот запросом.
DELETE t1
FROM table_name t1
JOIN table_name t2 ON t2.name = t1.name AND t2.id < t1.idSome 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.
This member has not yet provided a Biography. Assume it’s interesting and varied, and probably something to do with programming.
27 июля 2009 г.
MySQL
Не возможно использовать для обновления таблицу, в которой производишь выборку
Понадобилось ежесуточно высчитывать размер шрифта для оформления вывода жанров на Кинсбурге. И столкнулся с проблемой, что нельзя выбирать данные из таблицы, которая участвует в обновлении данных.
Вот примеры запросов
Пробуем в лоб:
update `categories` set `size` = (`count` / ((max(`count`) - min(`count`)) / 10)); ERROR 1111 (HY000): Invalid use of group functionПопробуем вложенный селект:
update `categories` set `size` = (select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`); ERROR 1093 (HY000): You can't specify target table 'categories' for update in FROM clauseПопробуем жоин:
update `categories` as `c1` JOIN `categories` as `c2` using(`category_id`) set `c1`.`size` = (`c2`.`count` / ((max(`c2`.`count`) - min(`c2`.`count`)) / 10)); ERROR 1111 (HY000): Invalid use of group functionПо отдельности все работает
Выводим «размер шрифта»:
select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`; +--------------------------------------------------------+ | (`count` / ((max(`count`) - min(`count`)) / 10)) | +--------------------------------------------------------+ | 3.47826087 | +--------------------------------------------------------+ 1 row in set (0.00 sec)Обновляем поле с размером шрифта:
update `categories` set `size` = 3.47826087; Query OK, 0 rows affected (0.00 sec) Rows matched: 21 Changed: 0 Warnings: 0Решение
Погуглив, и поломав голову с disc’ом, я пришел к следующему решению, представленное ниже.
Я решил выделить вычисление процента в переменную @percent, далее создал вьюху для таблицы «categories» и жойню таблицу с вьюхой:
-- создаем коэффициент деления set @percent = (select (max(`count`) - min(`count`)) / 10 from `categories`); -- создаем вьюху create view `categories_view` as select `category_id`, `count` from `categories`; -- жойним таблицу и вьюху, обновляя данные update `categories` as `c` join `categories_view` as `cv` using(`category_id`) set `c`.`size` = `cv`.`count` / @percent;Вот и все, приятного манокурения
UPD: Создадим процедуру и евент для этого события
/* Создаем вьюху и процедуру для установки размеров шрифта */ use kinsburg; /* создаем вьюху */ CREATE VIEW `categories_view` AS SELECT `category_id`, `count` FROM `categories`; /* создаем процедуру */ delimiter // DROP PROCEDURE IF EXISTS `updateCategorySize`// CREATE PROCEDURE `updateCategorySize` () BEGIN /* создаем коэффициент деления */ SET @percent = (SELECT (max(`count`) - min(`count`)) / 10 FROM `categories`); /* жойним таблицу и вьюху, обновляя данные */ UPDATE `categories` AS `c` JOIN `categories_view` AS `cv` USING(`category_id`) SET `c`.`size` = `cv`.`count` / @percent; END// delimiter ; /* создаем евент для вызова процедуры раз в сутки */ CREATE DEFINER = kinsburg@localhost EVENT `updateCategorySizeEvent` ON SCHEDULE EVERY 1 DAY DO CALL updateCategorySize;
