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.
Дата: 25.10.2013
Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru
В статье рассмотрены ошибки, возникающие из-за ограничений сервера MySQL на выполнение некоторых видов подзапросов. Даны рекомендации по их исправлению с примерами. Материал ориентирован на новичков.
MySQL error 1093
В MySQL нельзя изменять данные и одновременно делать выборку из той же таблицы в подзапросе. Запросы вида
DELETE FROM t WHERE col = (SELECT … FROM t …);
UPDATE t … WHERE col = (SELECT … FROM t …);
{INSERT|REPLACE} INTO t (SELECT … FROM t …);
приведут к ошибке
ERROR 1093 (HY000): You can‘t specify target table ‘t‘ for update in FROM clause.
Есть два варианта решения проблемы:
1. Универсальный способ, рекомендуемый в документации, — использовать вложенный подзапрос.
DELETE FROM t WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);
UPDATE t … WHERE col = (SELECT * FROM (SELECT … FROM t…) AS t1);
{INSERT|REPLACE} INTO t (SELECT * FROM (SELECT … FROM t…) AS t1);
В этом случае подзапрос к изменяемой таблице оказывается в части FROM и материализуется во временную таблицу в начале выполнения запроса. Т.о. при обновлении чтение данных будет идти из временной таблицы, а не из той, которая обновляется.
2. Для запросов UPDATE и DELETE можно использовать многотабличную форму. Например, для UPDATE запрос выше примет вид:
UPDATE t, (SELECT … FROM t …) t1 … WHERE t.col=t1.col;
По сути это тот же метод, что и предыдущий — подзапрос переносится в часть перечисления таблиц. Но кроме чуть более компактной записи многотабличная форма операторов UPDATE/DELETE в некоторых случаях позволяет вообще обойтись без подзапроса.
Примеры:
- мы хотим удалить из первой таблицы строки, не имеющие соответствия (по id) во второй.
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Для сравнения через подзапрос:
DELETE FROM t1 WHERE id IN (SELECT * FROM (SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL) as t_x);
- удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id
DELETE t1 FROM t t1 JOIN t t2
ON t1.col = t2.col AND t1.id < t2.id;через подзапрос
DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;
- а вот задача пометить важными статьи авторов, имеющих более 10 публикаций, без подзапроса не решается:
UPDATE articles SET important=1 WHERE author IN (SELECT * FROM (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t);
или
UPDATE articles, (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t SET important=1 WHERE author = t.author;
- в завершение рассмотрим пример, когда подзапрос находится в части SET. Например, для строк с id>10 мы хотим установить значение поля col равное значению этого поля для строки с id равным 2.
UPDATE t as t1 JOIN t as t2 ON t2.id=2 SET t1.col = t2.col WHERE t1.id > 10;
через подзапрос
UPDATE t
SET col = (SELECT * FROM (SELECT col FROM t WHERE id = 2) AS t1)
WHERE id = >10;
MySQL error 1235
В MySQL не реализована возможность использования IN/ALL/ANY/SOME вместе с подзапросом, содержащим LIMIT. Попытка выполнить такой запрос приведет к ошибке:
mysql> SELECT * FROM t1
-> WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn‘t yet support
‘LIMIT & IN/ALL/ANY/SOME subquery‘
Данное ограничение обходится путем переписывания запроса через JOIN с нужным подзапросом. Например:
SELECT * FROM t1 WHERE t1.col1 IN (SELECT col2 FROM t2 WHERE x);
можно переписать как
SELECT * FROM t1 JOIN (SELECT DISTINCT col2 FROM t2 WHERE x) t ON t1.col1=t.col2;
Если t2.col2 — уникальный ключ, то DISTINCT не потребуется и от подзапроса можно избавиться:
SELECT * FROM t1 JOIN t2 ON t1.col1=t.col2 WHERE t2.x;
На практике IN подзапросы более распространены и в большинстве случаев указанного правила будет достаточно. Некоторые виды ALL/ANY подзапросов могут потребовать более сложных преобразований, но переписать через JOIN можно любой их них.
Например, мы хотим найти страны в Европе, у которых каждый из трех самых крупных городов имеют население свыше миллиона. Если бы не запрет на использование LIMIT в ALL подзапросах, то решением было:
SELECT name FROM country WHERE continent=‘Europe’ AND
1000000 < ALL (SELECT population FROM city WHERE countrycode=code OREDR BY 1 DESC LIMIT 3);
через JOIN данный запрос примет вид:
SELECT country.name FROM country JOIN city ON countrycode=code
WHERE continent=‘Europe’ AND city.population>1000000
GROUP BY 1 HAVING(count(*)>2);
Замечание: Использовать вложенный подзапрос при mysql error 1235 является плохой идеей:
1. этот прием сработает только в случае независимых подзапросов;
2. на старых версиях (до MariaDB5.3/MySQL5.6) будет иметь худшую производительность, а в новых оптимизатор сам перепишет запрос через JOIN
P.S. Если после прочтения статьи ваш вопрос с MySQL Error 1093 или 1235 остался нерешенным, то задавайте его на форуме SQLinfo
Дата публикации: 25.10.2013
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
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.idAs an attempt to answer Difference data between adjacent rows
UPDATE Visits R
SET Duration = TIMEDIFF(
( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID LIMIT 1),
R.ReqTime
)
WHERE R.Duration IS NULL
leads to error #1093 - You can't specify target table 'R' for update in FROM clause.
What to do?
MySQL 5. Here is the Visits table:
CREATE TABLE `Visits` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`Session` int(10) unsigned NOT NULL COMMENT 'VisitsSessions',
`EventKind` enum('search','subsectionList','show','seance','like','superprice','toursList','abonementsList','abonement','popular','sales','tab','mainpage','other') NOT NULL COMMENT 'Also ''purchase'' in Bravo::Visits::Query',
`ReqTime` datetime NOT NULL,
`Duration` int(11) DEFAULT NULL COMMENT 'считается ПОТОМ, не при INSERT',
PRIMARY KEY (`ID`),
KEY `User` (`Session`),
KEY `EventKind` (`EventKind`),
KEY `LoadedTime` (`ReqTime`),
KEY `Duration` (`Duration`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='статистика для каждого отдельного юзера';
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.
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;
