MySQL error 1093 и 1235

MySQL ошибка 1093 и 1235

Автор: Василий Лукьянчиков , 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 col = (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