Błąd MySQL 1093 - Nie można określić tabeli docelowej do aktualizacji w klauzuli FROM


592

Mam story_categorybazę danych w mojej bazie danych z uszkodzonymi wpisami. Następne zapytanie zwraca uszkodzone wpisy:

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

Próbowałem je usunąć, wykonując:

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

Ale pojawia się następny błąd:

# 1093 - Nie można określić tabeli docelowej „story_category” do aktualizacji w klauzuli FROM

Jak mogę to przezwyciężyć?



2
Wygląda na to, że żądanie funkcji w narzędziu do śledzenia błędów MySQL jest tutaj: nie można zaktualizować tabeli i wybrać z tej samej tabeli w podzapytaniu
Ben Creasy

Odpowiedzi:


713

Aktualizacja: Ta odpowiedź obejmuje ogólną klasyfikację błędów. Aby uzyskać bardziej szczegółową odpowiedź na temat tego, jak najlepiej obsłużyć dokładne zapytanie OP, zobacz inne odpowiedzi na to pytanie

W MySQL nie można modyfikować tej samej tabeli, której używasz w części SELECT.
To zachowanie jest udokumentowane na stronie: http://dev.mysql.com/doc/refman/5.6/en/update.html

Może po prostu przyłączysz się do stołu

Jeśli logika jest wystarczająco prosta, aby zmienić kształt zapytania, należy utracić podzapytanie i dołączyć tabelę do siebie, stosując odpowiednie kryteria wyboru. Spowoduje to, że MySQL zobaczy tabelę jako dwie różne rzeczy, co pozwoli na destrukcyjne zmiany.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternatywnie spróbuj zagnieżdżić podkwerendę głębiej w klauzuli from ...

Jeśli absolutnie potrzebujesz podzapytania, istnieje obejście, ale jest brzydkie z kilku powodów, w tym wydajności:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

Zagnieżdżone podzapytanie w klauzuli FROM tworzy domyślną tabelę tymczasową , więc nie jest liczona jako ta sama tabela, którą aktualizujesz.

... ale uważaj na optymalizator zapytań

Pamiętaj jednak, że od MySQL 5.7.6 i późniejszych optymalizator może zoptymalizować podkwerendę i nadal wyświetla błąd. Na szczęście optimizer_switchzmienna może zostać użyta do wyłączenia tego zachowania; chociaż nie mogłem zalecić robienia tego jako czegoś więcej niż krótkoterminowej naprawy lub w przypadku drobnych, jednorazowych zadań.

SET optimizer_switch = 'derived_merge=off';

Dziękuję Peterowi V. Mørchowi za tę radę w komentarzach.

Przykładowa technika pochodzi od barona Schwartza, pierwotnie opublikowana w Nabble , sparafrazowana i rozszerzona tutaj.


1
Przegłosowałem tę odpowiedź, ponieważ musiałem usunąć elementy i nie mogłem uzyskać informacji z innej tabeli, musiałem wykonać podzapytanie z tej samej tabeli. Ponieważ to właśnie wyskakuje na górze podczas przeglądania Google w poszukiwaniu błędu, dostałem to najlepszą odpowiedź dla mnie i wielu ludzi próbujących zaktualizować podczas podkwerendowania z tego samego stołu.
HMR,

2
@ Cheekysoft, dlaczego zamiast tego zapisać wartości w zmiennych?
Pacerier

19
Uwaga: od MySQL 5.7.6 optymalizator może zoptymalizować pod-zapytanie i nadal wyświetla błąd, chyba że SET optimizer_switch = 'derived_merge=off';:-(
Peter V. Mørch

1
@ PeterV.Mørch Śledzenie mysqlserverteam.com/derived-tables-in-mysql-5-7 , w przypadku niektórych operacji, łączenie nie może nastąpić. Np. Podaj pochodnej tabeli manekina LIMIT (do inifity), a błąd nigdy nie wystąpi. To dość hackerskie i nadal istnieje ryzyko, że przyszłe wersje MySQL będą w końcu obsługiwać scalanie zapytań z LIMIT.
user2180613,

Czy możesz podać pełny przykład tego obejścia? AKTUALIZACJA tbl SET col = (WYBIERZ ... OD (WYBIERZ .... OD) AS x); wciąż otrzymuję błędy
JoelBonetR

310

NexusRex zapewnił bardzo dobre rozwiązanie do usuwania z łączenia z tej samej tabeli.

Jeśli to zrobisz:

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
)

dostaniesz błąd.

Ale jeśli owiniesz warunek jeszcze jednym, wybierz:

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
)

zrobiłoby to właściwą rzecz !!

Objaśnienie: Optymalizator kwerendy wykonuje pochodną optymalizację scalania dla pierwszego zapytania (co powoduje błąd z błędem), ale drugie zapytanie nie kwalifikuje się do optymalizacji pochodnej scalania . Dlatego optymalizator jest zmuszony najpierw wykonać podzapytanie.


6
Może to dlatego, że jestem dziś w trudnej sytuacji, ale to była najłatwiejsza odpowiedź, nawet jeśli nie jest to „najlepsza”.
Tyler V.

4
Działa świetnie, dzięki! Więc jaka jest tutaj logika? Jeśli jest zagnieżdżony jeszcze jeden poziom, to zostanie wykonany przed częścią zewnętrzną? A jeśli nie jest zagnieżdżony, to mySQL próbuje go uruchomić po tym, jak usunięcie ma blokadę w tabeli?
Flat Cat

43
Ten błąd i rozwiązanie nie mają logicznego sensu ... ale działa. Czasami zastanawiam się, jakie leki używają twórcy MySQL ...
Cerin

1
Zgadzam się z @Cerin .. jest to całkowicie absurdalne, ale działa.
FastTrack

@ ekononal Dziękuję za rozwiązanie, ale nie ma to dla mnie najmniejszego sensu, wygląda na to, że oszukujesz MySQL i on to akceptuje, lol
deFreitas

106

W inner joinpod-zapytaniu nie jest konieczne. Wygląda na to, że chcesz usunąć wpisy, story_categoryktórych category_idnie ma w categorytabeli.

Zrób to:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Zamiast tego:

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

5
To powinna być najlepsza odpowiedź! Może usuń pierwsze „zamiast”.
hoyhoy

1
Myślę, że nie DISTINCTjest tu potrzebny - dla lepszej wydajności;).
shA.t

1
Muszę być szalony. Odpowiedź jest taka sama jak w oryginale.
Jeff Lowery

To też jest odpowiedź dla mnie. Nie wpisuj where inkolumny identyfikatora, więc nie musisz subquery podstawowej tabeli.
Richard

@JeffLowery - pierwszy blok kodu jest tutaj odpowiedzią; jest to drugi blok kodu, który pochodzi z pytania.
ToolmakerSteve

95

Ostatnio musiałem zaktualizować rekordy w tej samej tabeli, co zrobiłem jak poniżej:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development' 
WHERE s.id = p.id;

11
Nie można tego tak po prostu zapisać UPDATE skills SET type='Development' WHERE type='Programming'; ? To nie wydaje się odpowiadać na pierwotne pytanie.
lilbyrdie

1
Wygląda na przesadę, @lilbyrdie ma rację - może być tylko UPDATE skills SET type='Development' WHERE type='Programming';. Nie rozumiem, dlaczego tak wielu ludzi nie myśli o tym, co robią ...
shadyyx,

1
to najlepsza odpowiedź tutaj, IMHO. Jego składnia jest łatwa do zrozumienia, możesz ponownie użyć poprzedniego oświadczenia i nie jest ograniczona do niektórych bardzo specyficznych przypadków.
Steffen Winkler,

2
Nawet jeśli przykładowy przypadek jest wątpliwy, zasadę najlepiej zrozumieć i wdrożyć w rzeczywistych scenariuszach. Pokazane zapytanie działa, ponieważ niejawne sprzężenie na liście tabel tworzy tymczasową tabelę dla podzapytania, zapewniając w ten sposób stabilne wyniki i unikając kolizji między pobieraniem a modyfikacją tej samej tabeli.
AnrDaemon

1
niezależnie od tego, co ktokolwiek mógłby powiedzieć o tym, że jest to przesada, nadal odpowiada na pytanie pod względem tytułu. Błąd, o którym wspominał PO, występuje również podczas próby aktualizacji przy użyciu tej samej tabeli w zapytaniu zagnieżdżonym
smac89,

35
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
)

3
Czy możesz wyjaśnić, dlaczego to działa, dlaczego po prostu zagnieżdżanie jeszcze jednego poziomu działa? To pytanie zostało już zadane jako komentarz do pytania @ EkoNoval, ale nikt nie odpowiedział. Może możesz pomóc.
Akshay Arora

@AkshayArora, przejdź do części pod nagłówkiem „Może możesz po prostu dołączyć do stołu do siebie” w odpowiedzi @ Cheekysoft. Powiedział UPDATE tbl AS a INNER JOIN tbl AS b ON .... SET a.col = b.col- działałoby to jako inny alias dla tej samej tabeli. Podobnie w odpowiedzi @ NexusRex pierwsze SELECTzapytanie działa jak tabela pochodna, w której story_categoryjest używana po raz drugi. Zatem błąd wymieniony w PO nie powinien mieć miejsca tutaj, prawda?
Istiaque Ahmed,

31

Jeśli nie możesz

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

ponieważ jest to ten sam stół, możesz oszukać i zrobić:

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[zaktualizuj lub usuń lub cokolwiek]


Najbardziej zrozumiała i logiczna realizacja wszystkich powyższych odpowiedzi. Prosto i do rzeczy.
Clain Dsilva,

To stało się teraz częścią mojego przepływu pracy. Zatrzymaj się na tym błędzie, przejdź do tej odpowiedzi i popraw zapytanie. Dzięki.
Vaibhav

13

Tak zrobiłem, aby zaktualizować wartość kolumny Priority o 1, jeśli jest ona> = 1 w tabeli i jej klauzuli WHERE, używając podzapytania w tej samej tabeli, aby upewnić się, że co najmniej jeden wiersz zawiera Priority = 1 (ponieważ to był warunek do sprawdzenia podczas aktualizacji):


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

Wiem, że to trochę brzydkie, ale działa dobrze.


1
@anonymous_reviewer: W przypadku podania [-1] lub nawet [+1] komuś komentarza, proszę również podać powód, dla którego go dałeś. Dzięki!!!
sactiw

1
-1, ponieważ jest to niepoprawne. Nie możesz modyfikować tej samej tabeli, której używasz w instrukcji SELECT.
Chris

1
@Chris Zweryfikowałem go na MySQL i działa dla mnie dobrze, więc poprosiłbym cię o weryfikację na swoim końcu, a następnie twierdzę, że jest poprawny lub niepoprawny. Dzięki!!!
sactiw

1
Na dole tej strony jest napisane: „Obecnie nie można zaktualizować tabeli i wybrać tej samej tabeli w podzapytaniu”. - i doświadczyłem tego wielokrotnie. dev.mysql.com/doc/refman/5.0/en/update.html
Chris

19
@Chris Wiem o tym, ale istnieje obejście tego problemu, które właśnie próbowałem pokazać za pomocą zapytania „UPDATE” i wierzcie, że działa dobrze. Nie sądzę, żebyś naprawdę próbował w ogóle zweryfikować moje zapytanie.
sactiw

6

Najprostszym sposobem na to jest użycie aliasu tabeli, gdy odwołujesz się do nadrzędnej tabeli zapytań wewnątrz zapytania podrzędnego.

Przykład:

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Zmień na:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

5

Możesz wstawić żądane identyfikatory wierszy do tabeli tymczasowej, a następnie usunąć wszystkie wiersze znajdujące się w tej tabeli.

co może oznaczać @Cheekysoft, robiąc to w dwóch krokach.


3

Zgodnie ze składnią Mysql UPDATE połączoną przez @CheekySoft, napisano na samym dole.

Obecnie nie można aktualizować tabeli i wybierać z tej samej tabeli w podzapytaniu.

Wydaje mi się, że usuwasz z store_category, wciąż wybierając z niej w unii.


3

W przypadku konkretnego zapytania, które OP próbuje osiągnąć, idealnym i najbardziej wydajnym sposobem na to NIE jest wcale korzystanie z podzapytania.

Oto LEFT JOINwersje dwóch zapytań PO:

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

Uwaga: DELETE sogranicza operacje usuwania do story_categorytabeli.
Dokumentacja

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

1
Zaskoczony, że nie ma więcej głosów pozytywnych. Należy również zauważyć, że składnia wielostołowa działa również z UPDATEinstrukcjami i połączonymi pod-zapytaniami. Umożliwiając działanie LEFT JOIN ( SELECT ... )w przeciwieństwie do WHERE IN( SELECT ... ), dzięki czemu implementacja jest przydatna w wielu przypadkach użycia.
fyrye

2

Jeśli coś nie działa, przechodząc przez drzwi frontowe, weź tylne drzwi:

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;

To jest szybkie. Im większe dane, tym lepiej.


11
Właśnie zgubiłeś wszystkie klucze obce i być może masz także pewne usuwanie kaskadowe.
Walf,

2

Spróbuj zapisać wynik instrukcji Select w osobnej zmiennej, a następnie użyj go do usunięcia zapytania.


2

Spróbuj tego

DELETE FROM story_category 
WHERE category_id NOT IN (
SELECT DISTINCT category.id 
FROM (SELECT * FROM STORY_CATEGORY) sc;

1

co powiesz na to zapytanie? Mam nadzieję, że to pomoże

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL

Wynik pokazuje: „# 1064 - Wystąpił błąd w składni SQL; sprawdź instrukcję, która odpowiada twojej wersji serwera MariaDB, aby uzyskać prawidłową składnię do użycia w pobliżu „LEWEGO DOŁĄCZU (WYBIERZ kategorie.id Z kategorii) cat ON story_category.id = cat.” w linii 1 '
Istiaque Ahmed

Podczas korzystania z funkcji usuwania wielu tabel należy określić odpowiednie tabele. DELETE story_category FROM ...Jednak dołączył sub-query nie jest konieczne w tym kontekście i może być wykonywane za pomocą LEFT JOIN category AS cat ON cat.id = story_category.category_id WHERE cat.id IS NULLurządzenia, należy przestrzegać dołączyć kryteria w odpowiedzi błędnego odwołaniastory_category.id = cat.id
fyrye

0

Jeśli chodzi o sprawy, chcesz usunąć wiersze story_category, które nie istnieją w category.

Oto twoje oryginalne zapytanie, aby zidentyfikować wiersze do usunięcia:

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

Łączenie NOT INz podzapytaniem, które JOINjest oryginalną tabelą, wydaje się niepotrzebnie zawiłe. Można to wyrazić w prostszy sposób za pomocą not existsi skorelowanego podkwerendy:

select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id);

Teraz łatwo jest zmienić to w deletestwierdzenie:

delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);    

To pytanie działałoby na dowolnej wersji MySQL, a także w większości innych baz danych, które znam.

Demo na DB Fiddle :

-- set-up
create table story_category(category_id int);
create table category (id int);
insert into story_category values (1), (2), (3), (4), (5);
insert into category values (4), (5), (6), (7);

-- your original query to identify offending rows
SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);
| kategoria_id |
| ----------: |
| 1 |
| 2 |
| 3 |
-- a functionally-equivalent, simpler query for this
select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id)
| kategoria_id |
| ----------: |
| 1 |
| 2 |
| 3 |
-- the delete query
delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);

-- outcome
select * from story_category;
| kategoria_id |
| ----------: |
| 4 |
| 5 |
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.