MySQL DELETE FROM z podzapytaniem jako warunkiem


85

Próbuję wykonać takie zapytanie:

DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (
    SELECT DISTINCT(th1.tid)
    FROM term_hierarchy AS th1
    INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
    WHERE th1.parent = 1015
);

Jak zapewne możesz stwierdzić, chcę usunąć relację rodzicielską do 1015, jeśli ten sam czas ma innych rodziców. Jednak powoduje to błąd składni:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS th
WHERE th.parent = 1015 AND th.tid IN (
  SELECT DISTINCT(th1.tid)
  FROM ter' at line 1

Sprawdziłem dokumentację i sam uruchomiłem podzapytanie i wydaje się, że wszystko działa. Czy ktoś może dowiedzieć się, co tu jest nie tak?

Aktualizacja : zgodnie z poniższą odpowiedzią MySQL nie pozwala na użycie tabeli, z której usuwasz, w podzapytaniu dla warunku.


2
Uwaga : Dobra odpowiedź na dole stackoverflow.com/a/4471359/956397 po prostu dodaj alias tabeli poDELETE t FROM table t ...
PiTheNumber.

Odpowiedzi:


38

Nie można określić tabeli docelowej do usunięcia.

Obejście problemu

create table term_hierarchy_backup (tid int(10)); <- check data type

insert into term_hierarchy_backup 
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015;

DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);

oboje mamy rację - zobacz poniżej jego komentarz do mojej odpowiedzi. Zarówno składnia aliasów, jak i logika były problematyczne :)
JNK

Tak, wygląda na to, że usuwanie przez podzapytanie nie jest obecnie możliwe w MySQL - dzięki za przyjrzenie się temu :)
mikl

czy „USUŃ Z hierarchii terminów AS th” w ostatniej linii nie ma tego samego problemu? Otrzymuję błąd składni taki sam jak OP.
malhal

Należy dodać indeks do term_hierarchy_backup.tid.
Roman Newaza,

1
Jestem w stanie to sprawdzić, że nie jest to możliwe w 2019 na MariaDB 10.3.14 lub MySQL Community Server 5.7.27
alpham8

284

Dla innych, którzy uważają, że to pytanie chcą usunąć podczas korzystania z podzapytania, zostawiam ten przykład przechytrzenia MySQL (nawet jeśli niektórzy uważają, że nie można tego zrobić):

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
             FROM tableE
             WHERE arg = 1 AND foo = 'bar');

wyświetli błąd:

ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause

Jednak to zapytanie:

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
             FROM (SELECT id
                   FROM tableE
                   WHERE arg = 1 AND foo = 'bar') x);

będzie działać dobrze:

Query OK, 1 row affected (3.91 sec)

Umieść podzapytanie w dodatkowym podzapytaniu (tutaj nazwanym x), a MySQL z radością zrobi to, o co poprosisz.


10
Zajęło mi to trochę czasu, ale udało mi się. Ważne: 1) Pierwsza tabela musi mieć alias, jak pokazano tutaj z "e", 2) "x" na końcu nie jest symbolem zastępczym, jest aliasem dla tabeli tymczasowej utworzonej przez podzapytanie "(SELECT id FROM tableE GDZIE arg = 1 AND foo = 'bar') ".
Tilman Hausherr

3
Dlaczego to działa? To dla mnie bardzo się zmienia, ale co więcej, to nie powinno działać. To nie praca, ale to nie powinno.
donatJ

1
nie do wiary. to faktycznie działa! ale nie jesteś zmuszony do aliasowania tabeli przez e ... możesz użyć dowolnego aliasu.
Andrei Sandulescu

1
@jakabadambalazs Kiedy to wymyśliłem, rozumowałem, że podzapytanie zaczynające się od „SELECT id” kończy się i zwraca listę identyfikatorów, a tym samym zwalnia blokadę tabeli, z której chcesz usunąć.
CodeReaper,

9
@jakabadambalazs: Nie możemy użyć tej samej tabeli ( e) w DELETE i jej sub-SELECT. my puszka jednak użyć sub-sub-select, aby utworzyć tabelę tymczasową ( x), i wykorzystanie że dla sub-select.
Steve Almond

39

Alias ​​należy umieścić po DELETEsłowie kluczowym:

DELETE th
FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN 
(
    SELECT DISTINCT(th1.tid)
    FROM term_hierarchy AS th1
    INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
    WHERE th1.parent = 1015
);

3
To jest dobra odpowiedź. Właściwy aliasing znacznie ułatwi rozwiązanie problemów podobnych do oryginalnego postu. (jak Mine.)
usumoio

10

Musisz ponownie odwołać się do aliasu w instrukcji delete, na przykład:

DELETE th FROM term_hierarchy AS th
....

Jak opisano w dokumentacji MySQL.


nie chodzi o alias, sprawdź ponownie OP
ajreal

@ajreal - tak, i proszę zauważyć, że błąd zaczyna się od definicji aliasu, a dokumentacja MySQL wyraźnie stwierdza, że ​​musisz użyć aliasu w instrukcji DELETE, a także z klauzuli FROM. Dziękuję za głos przeciw.
JNK

po prostu zrób to, delete from your_table as t1 where t1.id in(select t2.id from your_table t2);co dostałeś?
ajreal

4
Dokumentacja jasno stwierdza; Currently, you cannot delete from a table and select from the same table in a subquery. dev.mysql.com/doc/refman/5.5/en/delete.html
Björn

1
nie musisz naprawiać aliasu, po prostu nie określaj tabeli docelowej do wybierania podczas usuwania ... to jest prawdziwy problem
ajreal

7

Podszedłem do tego w nieco inny sposób i to zadziałało;

Musiałem usunąć secure_linksz mojej tabeli, która odnosiła się do conditionstabeli, w której nie było już żadnych wierszy warunków. Zasadniczo skrypt porządkowy. To spowodowało błąd - nie można określić tabeli docelowej do usunięcia.

Szukając tutaj inspiracji, wpadłem na poniższe zapytanie i działa dobrze. Dzieje się tak, ponieważ tworzy tymczasową tabelę, sl1która jest używana jako odniesienie dla DELETE.

DELETE FROM `secure_links` WHERE `secure_links`.`link_id` IN 
            (
            SELECT
                `sl1`.`link_id` 
            FROM 
                (
                SELECT 

                    `sl2`.`link_id` 

                FROM 
                    `secure_links` AS `sl2` 
                    LEFT JOIN `conditions` ON `conditions`.`job` = `sl2`.`job` 

                WHERE 

                    `sl2`.`action` = 'something' AND 
                    `conditions`.`ref` IS NULL 
                ) AS `sl1`
            )

Pracuje dla mnie.


5

Czy klauzula „in” w delete ... gdzie jest skrajnie nieefektywna, jeśli ma być zwrócona duża liczba wartości z podzapytania? Nie wiesz, dlaczego chcesz usunąć tylko wewnętrzne (lub prawe) połączenie z powrotem z oryginalną tabelą z podzapytania na identyfikatorze, a nie „in (podzapytanie)”.?

DELETE T FROM Target AS T
RIGHT JOIN (full subquery already listed for the in() clause in answers above) ` AS TT ON (TT.ID = T.ID)

I może odpowiedź na to pytanie znajduje się w "MySQL nie pozwala na to", jednak dla mnie działa dobrze. PODEJŚCIE upewniam się, że w pełni wyjaśnię, co należy usunąć (USUŃ T FROM Target AS T). Usuń za pomocą dołączenia w MySQL wyjaśnia problem USUŃ / DOŁĄCZ.


2

Jeśli chcesz to zrobić z 2 zapytaniami, zawsze możesz zrobić coś podobnego do tego:

1) pobierz identyfikatory ze stołu za pomocą:

SELECT group_concat(id) as csv_result FROM your_table WHERE whatever = 'test' ...

Następnie skopiuj wynik za pomocą myszy / klawiatury lub języka programowania do XXX poniżej:

2) DELETE FROM your_table WHERE id IN ( XXX )

Może mógłbyś to zrobić w jednym zapytaniu, ale to jest to, co wolę.


0

@CodeReaper, @BennyHill: Działa zgodnie z oczekiwaniami.

Zastanawiam się jednak, jak skomplikowany czas ma miliony wierszy w tabeli? Najwyraźniej zajęło to około 5ms5k rekordów na poprawnie zindeksowanej tabeli.

Moje zapytanie:

SET status = '1'
WHERE id IN (
    SELECT id
    FROM (
      SELECT c2.id FROM clusters as c2
      WHERE c2.assign_to_user_id IS NOT NULL
        AND c2.id NOT IN (
         SELECT c1.id FROM clusters AS c1
           LEFT JOIN cluster_flags as cf on c1.last_flag_id = cf.id
           LEFT JOIN flag_types as ft on ft.id = cf.flag_type_id
         WHERE ft.slug = 'closed'
         )
      ) x)```

Or is there something we can improve on my query above?

0

możesz użyć aliasu w ten sposób w instrukcji delete

DELETE  th.*
FROM term_hierarchy th
INNER JOIN term_hierarchy th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th.parent = 1015;
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.