Дата: 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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
у меня есть таблица story_category в моей базе данных с поврежденных записей. Следующий запрос возвращает поврежденные записи:
SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
Я попытался удалить их выполнение:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category
INNER JOIN story_category ON category_id=category.id);
но я получаю следующую ошибку:
#1093 — вы не можете указать целевую таблицу «story_category» для обновления в предложении FROM
как я могу преодолеть это?
14 ответов
Update: этот ответ охватывает общую классификацию ошибок. Для более конкретного ответа о том, как лучше всего обрабатывать точный запрос OP, см. другие ответы на этот вопрос
в MySQL вы не можете изменить ту же таблицу, которую вы используете в выбранной части.
Это поведение задокументировано на:
http://dev.mysql.com/doc/refman/5.6/en/update.html
возможно, вы можете просто присоединиться к столу, чтобы сам
если логика достаточно проста, чтобы переформулировать запрос, потеряйте подзапрос и присоедините таблицу к себе, используя соответствующие критерии выбора. Это заставит MySQL видеть таблицу как две разные вещи, позволяя деструктивным изменениям идти вперед.
UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col
кроме того, попробуйте вложить подзапрос глубже в предложение from …
если вы абсолютно нужен подзапрос, есть обходной путь, но это
некрасиво несколько причин, в том числе:
UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);
вложенный подзапрос в предложении FROM создает неявные временные
таблица, поэтому он не считается той же таблицей, которую вы обновляете.
… но следите за оптимизатором запросов
однако, остерегайтесь этого от MySQL 5.7.6 и далее, оптимизатор может оптимизировать подзапрос и все равно дать вам ошибку. К счастью,optimizer_switch переменная может использоваться для переключения от этого поведения; хотя я не мог бы рекомендовать делать это как что-то большее, чем краткосрочное исправление или для небольших одноразовых задач.
SET optimizer_switch = 'derived_merge=off';
спасибо Петер В. Мерх для этого совета в комментариях.
пример техники был от Барона Шварца,первоначально опубликовано в Nabble, перефразировано и расширено здесь.
NexusRex предоставил очень хорошее решение для удаления с помощью join из той же таблицы.
Если вы это сделаете:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
)
вы получите сообщение об ошибке.
но если вы обернете условие еще одним выбором:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
) AS c
)
это было бы правильно!!
объяснение: оптимизатор запросов делает оптимизация производного слияния для первого запроса (что приводит к сбою с ошибкой), но второй запрос не подходит для оптимизация производного слияния. Следовательно, оптимизатор вынужден сначала выполнить подзапрос.
на inner join в вашем подзапросе нет необходимости. Похоже, вы хотите удалить записи в story_category здесь category_id не в category таблица.
этого:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category);
вместо этого:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
недавно мне пришлось обновлять записи в той же таблице, что и ниже:
UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;
83
автор: Pratik Khadloya
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)
Если вы не можете сделать
UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);
потому что это одна и та же таблица, вы можете обмануть и сделать:
UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[обновить или удалить или что угодно]
Это то, что я сделал для обновления значения столбца приоритета на 1, если это >=1 в таблице и в его предложении WHERE, используя подзапрос в той же таблице, чтобы убедиться, что по крайней мере одна строка содержит приоритет=1 (потому что это условие должно быть проверено при выполнении обновления) :
UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);
Я знаю, что это немного уродливо, но он работает нормально.
вы можете вставить нужные строки идентификаторов во временную таблицу, а затем удалить все строки, которые находятся в этой таблице.
Что может быть тем, что @Cheekysoft имел в виду, делая это в два шага.
по данным синтаксис обновления Mysql связанный @CheekySoft, он говорит прямо внизу.
в настоящее время, вы не можете обновить таблицу и выбрать из той же таблицы в подзапросе.
Я думаю, вы удаляете из store_category, все еще выбирая из него в Союзе.
если что-то не работает, проходя через парадную дверь, тогда возьмите заднюю дверь:
drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);
insert into apples values('fuji', 5), ('gala', 6);
drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;
update apples_new
set price = (select price from apples where variety = 'gala')
where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;
это быстро. Чем больше данных, тем лучше.
попробуйте сохранить результат оператора Select в отдельной переменной, а затем использовать его для запроса удаления.
1
автор: lipika chakraborty
Как насчет этого запроса надеюсь, что это поможет
DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
0
автор: Melvin Angelo Jabonillo
самый простой способ сделать это-использовать псевдоним таблицы, когда вы ссылаетесь на таблицу родительского запроса внутри подзапроса.
пример :
insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));
меняем его на:
insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));
попробуй такое
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;
Stuck with MySQL error code 1093? We can help you fix it.
This error is mainly related to the subqueries.
At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.
Today, let’s see how our Support Engineers fix MySQL Error Code 1093 for our customers.
Why MySQL error code 1093?
While dealing with MySQL, we may encounter some errors.
Today, we shall discuss MySQL error 1093 and see how we fix it.
This MySQL error code is mainly related to subqueries. It appears while updating or deleting a table, and while building a subquery for that using the same table.
How we fix it?
Recently, one of our customers approached us with a request to fix an error like the one shown below, which he was getting while updating a table.
So, our Engineers checked in detail and found that he was using a subquery build with the same table which he wanted to update.
So, we deleted the reference of the table to update from subquery and this fixed the error.
Also, we handled a situation where a customer approached us with the same MySQL error code 1093.
He said this error appeared while trying to delete a table using a query as given below:
DELETE FROM table_name where column_name IN (SELECT column_name FROM table_name WHERE column_name > 10);We changed the query as below because the customer is trying to delete the rows from the same data source which a subquery refers to, and that cannot be deleted.
DELETE FROM table_name where column_name IN ( SELECT * FROM (SELECT column_name FROM table_name WHERE column_name > 10) AS X) ;Finally, this fixed the error.
[Need more assistance to fix the MySQL Error code 1093?- We’re available 24/7.]
Conclusion
In short, this error can be fixed by deleting the reference of the table to update from the subquery. Also, we saw how our Support Engineers find fix for this MySQL error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;