Mysql ошибка 1093

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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Получил такую ошибку при выполнении 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.id