Ошибка 1093 mysql

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

Источник

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.

Источник

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.

MySQL error code 1093

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»;

Источник

MySQL Error 1093 is a common error that occurs when trying to update a table in MySQL. This error is caused when the target table is used in a subquery that is also used to update the same table. This guide will provide an explanation of the error and offer some solutions to the problem.

Explanation of the Error

The MySQL Error 1093 occurs when the target table is used in a subquery that is also used to update the same table. This creates a problem since MySQL does not allow updating a table that is being used in a subquery. The error message will look something like this:

Error 1093: You can't specify target table 'table_name' for update in FROM clause

Solutions

There are a few different solutions to this problem, depending on your specific situation. Here are a few options:

1. Use a Temporary Table

One solution is to use a temporary table to store the results of the subquery. This will prevent the target table from being used in the subquery, which eliminates the error. Here is an example:

CREATE TEMPORARY TABLE temp_table
SELECT * FROM table_name WHERE condition;

UPDATE table_name
SET column_name = new_value
WHERE id IN (SELECT id FROM temp_table);

2. Use a Derived Table

Another solution is to use a derived table. This involves creating a subquery and then using it as a table in the main query. Here is an example:

UPDATE table_name
SET column_name = new_value
WHERE id IN (SELECT id FROM (SELECT * FROM table_name WHERE condition) AS derived_table);

3. Use a JOIN

A third solution is to use a JOIN statement instead of a subquery. This will allow you to update the target table without using it in a subquery. Here is an example:

UPDATE table_name
JOIN other_table ON table_name.id = other_table.id
SET table_name.column_name = new_value
WHERE table_name.condition;

Conclusion

MySQL Error 1093 can be frustrating, but there are several solutions available. By using a temporary table, a derived table, or a JOIN statement, you can update your target table without encountering this error. Keep in mind that the solution you choose will depend on your specific situation, so it’s important to evaluate each option carefully. With these solutions in mind, you can overcome this error and get back to working with your MySQL database.

Источник

MySQL Error 1093 is a common error that occurs when trying to execute an UPDATE statement with a subquery in the FROM clause. This error occurs because MySQL does not allow the target table of an UPDATE statement to be referenced in the FROM clause of the same statement. In this case, the database management system returns the «Can’t specify target table for update in FROM clause» error message, indicating that the statement is invalid and cannot be executed.

Method 1: Use a derived table

To fix the MySQL Error 1093 — Can’t specify target table for update in FROM clause, you can use a derived table. Here’s how to do it:

UPDATE table_name
SET column_name = 'new_value'
WHERE primary_key_column IN (
  SELECT * FROM (
    SELECT primary_key_column
    FROM table_name
    WHERE some_condition
  ) AS derived_table
);

Explanation:

  1. First, we create a derived table by wrapping the inner SELECT statement in another SELECT statement.
  2. We give the derived table an alias (in this case, «derived_table»).
  3. We use the derived table in the IN clause of the outer UPDATE statement.
  4. The derived table contains only the primary key column from the original table that meets the WHERE condition.
  5. The outer UPDATE statement then updates the rows in the original table that match the primary key values in the derived table.

Here’s another example:

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id IN (
  SELECT * FROM (
    SELECT employee_id
    FROM employees
    WHERE department = 'Sales'
  ) AS derived_table
);

This example increases the salary of employees in the Sales department by 10%.

In summary, to fix the MySQL Error 1093 — Can’t specify target table for update in FROM clause, you can use a derived table. The derived table contains only the primary key column from the original table that meets the WHERE condition, and it is used in the IN clause of the outer UPDATE statement.

Method 2: Use a join in the UPDATE statement

To fix the MySQL Error 1093 — Can’t specify target table for update in FROM clause, you can use a join in the UPDATE statement. Here’s an example code:

UPDATE table1
JOIN table2 ON table1.id = table2.id
SET table1.column = 'new value'
WHERE table2.column = 'condition';

In this code, table1 is the table you want to update, and table2 is used to join with table1. The ON clause specifies the join condition. The SET clause sets the new value for the column you want to update. The WHERE clause specifies the condition to filter the rows to be updated.

You can also use aliases for table names to make the code more readable:

UPDATE t1
JOIN t2 ON t1.id = t2.id
SET t1.column = 'new value'
WHERE t2.column = 'condition';

Note that you cannot use the same table in the FROM clause and the UPDATE statement, hence the need for a join.

Using a join in the UPDATE statement is a powerful technique that allows you to update rows based on conditions from multiple tables. However, it requires a good understanding of SQL syntax and the data model of your database. Make sure to test your code thoroughly before applying it to production data.

Method 3: Use a temporary table

To fix MySQL Error 1093 — Can’t specify target table for update in FROM clause, we can use a temporary table. Here are the steps to do it:

  1. Create a temporary table with the result of the inner query:
CREATE TEMPORARY TABLE temp_table
SELECT column1, column2
FROM table1
WHERE condition;
  1. Update the target table using the temporary table:
UPDATE table1
INNER JOIN temp_table ON table1.id = temp_table.id
SET table1.column1 = temp_table.column1,
    table1.column2 = temp_table.column2
WHERE table1.id = temp_table.id;
  1. Drop the temporary table:
DROP TEMPORARY TABLE IF EXISTS temp_table;

Here’s an example code:

CREATE TEMPORARY TABLE temp_table
SELECT id, column1, column2
FROM table1
WHERE condition;

UPDATE table1
INNER JOIN temp_table ON table1.id = temp_table.id
SET table1.column1 = temp_table.column1,
    table1.column2 = temp_table.column2
WHERE table1.id = temp_table.id;

DROP TEMPORARY TABLE IF EXISTS temp_table;

Explanation:

We create a temporary table called «temp_table» that stores the result of the inner query. Then, we join this temporary table with the target table «table1» using the «id» column and update the columns «column1» and «column2» of «table1» with the corresponding values from «temp_table». Finally, we drop the temporary table using the «DROP TEMPORARY TABLE» statement.

This method allows us to avoid the error caused by referencing the target table in the inner query. By using a temporary table, we can update the target table without any issues.

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1093 isuzu
  • Ошибка 1092040 2040
  • Ошибка 1103 critical ops
  • Ошибка 1107 интернет
  • Ошибка 1102 паджеро 4