MySQL Error 1093 - Can't specify target table for update in FROM clause

mysql subquery sql-delete mysql-error-1093

397984 просмотра

15 ответа

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

I tried to delete them executing:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);

But I get the next error:

#1093 - You can't specify target table 'story_category' for update in FROM clause

How can I overcome this?

Автор: Sergio del Amo Источник Размещён: 12.07.2019 08:53

Ответы (15)


662 плюса

Решение

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

Если логика достаточно проста, чтобы изменить запрос, потеряйте подзапрос и присоедините таблицу к себе, используя соответствующие критерии выбора. Это приведет к тому, что 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';

Спасибо Peter V. Mørch за этот совет в комментариях.

Пример техники был от барона Шварца, первоначально опубликованный в Набле , перефразированный и расширенный здесь.

Автор: Cheekysoft Размещён: 05.09.2008 10:07

258 плюса

NexusRex предоставил очень хорошее решение для удаления с помощью объединения из той же таблицы.

Если вы делаете это:

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
)

это сделало бы правильную вещь !!

Объяснение: Оптимизатор запросов выполняет оптимизацию производного слияния для первого запроса (что приводит к сбою при ошибке), но второй запрос не подходит для оптимизации производного слияния . Следовательно, оптимизатор сначала должен выполнить подзапрос.

Автор: Ekonoval Размещён: 23.03.2012 05:23

102 плюса

В 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);
Автор: DanDarc Размещён: 03.06.2009 09:40

89 плюса

Недавно мне пришлось обновить записи в той же таблице, я сделал это, как показано ниже:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development' 
WHERE s.id = p.id;
Автор: Pratik Khadloya Размещён: 04.10.2012 11:39

33 плюса

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
)
Автор: NexusRex Размещён: 23.12.2011 08:03

21 плюса

Если вы не можете сделать

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)

[обновить или удалить или что-то еще]

Автор: Sequoya Размещён: 25.04.2017 11:59

12 плюса

Это то, что я сделал для обновления значения столбца Приоритет на 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);

Я знаю, что это немного некрасиво, но работает нормально.

Автор: sactiw Размещён: 02.09.2010 07:31

5 плюса

Вы можете вставить идентификаторы нужных строк во временную таблицу, а затем удалить все строки, найденные в этой таблице.

что может означать то, что @Cheekysoft имел в виду, делая это в два этапа.

Автор: YonahW Размещён: 05.09.2008 10:25

4 плюса

Согласно синтаксису ОБНОВЛЕНИЯ Mysql, связанному с @CheekySoft, он написан прямо внизу.

В настоящее время вы не можете обновить таблицу и выбрать одну и ту же таблицу в подзапросе.

Я предполагаю, что вы удаляете из store_category, все еще выбирая его в объединении.

Автор: briankip Размещён: 08.01.2015 01:18

2 плюса

Если что-то не работает, когда вы проходите через парадную дверь, возьмите заднюю дверь:

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;

Это быстро. Чем больше данных, тем лучше.

Автор: Tom Schaefer Размещён: 29.03.2009 02:26

2 плюса

Самый простой способ сделать это - использовать псевдоним таблицы, когда вы ссылаетесь на родительскую таблицу запросов внутри подзапроса.

Пример :

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));
Автор: S.Roshanth Размещён: 11.01.2018 02:14

2 плюса

попробуй это

DELETE FROM story_category 
WHERE category_id NOT IN (
SELECT DISTINCT category.id 
FROM (SELECT * FROM STORY_CATEGORY) sc;
Автор: Sameer Khanal Размещён: 23.01.2018 01:14

1 плюс

Попробуйте сохранить результат оператора Select в отдельной переменной, а затем использовать его для запроса на удаление.

Автор: lipika chakraborty Размещён: 31.05.2016 11:35

1 плюс

как насчет этого запроса надеюсь, что это поможет

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
Автор: Melvin Angelo Jabonillo Размещён: 11.05.2017 01:55

0 плюса

Для конкретного запроса, который пытается выполнить OP, идеальный и наиболее эффективный способ сделать это - НЕ использовать подзапрос вообще. Вот LEFT JOINверсии двух запросов ОП:

SELECT s.* FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;

DELETE s FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;
Автор: Doin Размещён: 10.07.2019 08:16
Вопросы из категории :
32x32