Jak usunąć z Select w MySQL?

86

Ten kod nie działa z MySQL 5.0, jak go ponownie napisać, aby działał

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 ))

Chcę usunąć kolumny, które nie mają unikalnego identyfikatora. Dodam, że przeważnie jest to jedyny identyfikator (próbowałem w składni i też nie działa).

— IAdapter
źródło

213

SELECTZapytania (sub) zwracają zestawy wyników . Więc musisz użyć IN, a nie =w swojej WHEREklauzuli.

Ponadto, jak pokazano w tej odpowiedzi , nie można modyfikować tej samej tabeli z podzapytania w ramach tego samego zapytania. Jednakże, można albo SELECTnastępnie DELETEw oddzielnych zapytań lub gniazda innego podzapytania i alias wynikiem wewnętrzny podzapytania (patrzy raczej hacky, choć):

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

Lub użyj złączeń zgodnie z sugestią Mchl .

— BoltClock
źródło

1

Miałem stolik ze 150 zduplikowanymi kluczami. Wykonałem powyższe zapytanie i powiedziało, że "dotyczy 144 wierszy", ale wciąż są zduplikowane klucze. Więc ponownie wykonałem zapytanie i mówi, że dotyczy to 5 wierszy, znowu: dotyczy to 1 wiersza. Potem zniknęły wszystkie zduplikowane klucze. Dlaczego to?

— Alex

Dzieje się tak, ponieważ usuwasz tylko 1 wpis z każdego zestawu duplikatów:SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )

— havvg

# 1248 - Każdy pochodzi tabela musi mieć swój własny alias

— thang

@thang: Dlatego właśnie powiedziałem, aby aliasować wewnętrzne podzapytanie.

— BoltClock

1

Czy możesz wyjaśnić, co robi „As p”?

— Cricketer

22

DELETE 
  p1
  FROM posts AS p1 
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)

— Mchl
źródło

Wydaje się, że to działa, ale jestem zdezorientowany składnią i nie mogę znaleźć żadnych innych zasobów, aby to wyjaśnić. CROSS JOINnajwyraźniej wykonuje sprzężenie kartezjańskie, więc wygląda na to, że może to wykonać niepotrzebną pracę lub działać nieoptymalnie? Czy ktoś mógłby to wyjaśnić?

— wintron

Zrobi produkt kartezjański tylko wtedy, gdy nie ma USINGklauzuli. Z USINGiloczynem ogranicza się do par o tej samej wartości w idkolumnie, więc w rzeczywistości jest bardzo ograniczony.

— Mchl

Czy mógłbyś zrobić to samo ze złączeniem wewnętrznym? IE

DELETE    p1   FROM posts AS p1 INNER JOIN (   SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1 ) AS p2 ON p2.ID=p1.ID

— Kodos Johnson

1

@ Andrew: Tak. Funkcjonalnie te łączenia są dokładnie takie same.

— Mchl

5

możesz użyć sprzężenia wewnętrznego:

DELETE 
    ps 
FROM 
    posts ps INNER JOIN 
         (SELECT 
           distinct id 
         FROM 
             posts 
         GROUP BY id  
      HAVING COUNT(id) > 1 ) dubids on dubids.id = ps.id

— Charif DZ
źródło

0

Jeśli chcesz usunąć wszystkie duplikaty, ale po jednym z każdego zestawu duplikatów, oto jedno rozwiązanie:

DELETE posts
FROM posts
LEFT JOIN (
    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) = 1

    UNION

    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) != 1
) AS duplicate USING (id)
WHERE duplicate.id IS NULL;

— havvg
źródło