T-SQL: Wybieranie wierszy do usunięcia za pomocą złączeń


494

Scenariusz:

Powiedzmy, że mam dwie tabele, TableA i TableB. Klucz podstawowy tabeli B to pojedyncza kolumna (BId) i kolumna klucza obcego w tabeli A.

W mojej sytuacji chcę usunąć wszystkie wiersze w tabeli A, które są powiązane z określonymi wierszami w tabeli B: Czy mogę to zrobić za pomocą sprzężeń? Usunąć wszystkie wiersze wciągnięte ze złączeń?

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

Czy jestem zmuszony to zrobić:

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

Powód, dla którego pytam, wydaje mi się, że pierwsza opcja byłaby o wiele bardziej skuteczna w przypadku większych tabel.

Dzięki!

Odpowiedzi:


722
DELETE TableA
FROM   TableA a
       INNER JOIN TableB b
               ON b.Bid = a.Bid
                  AND [my filter condition] 

powinno działać


1
Użyłem And [mój warunek filtru] na złączeniu zamiast klauzuli Where. Wyobrażam sobie, że oba zadziałałyby, ale warunek filtru na złączeniu ograniczy twoje wyniki ze złączenia.
TheTXI

10
Jedno pytanie. Dlaczego musimy napisać „DELETE TableA FROM” zamiast „DELETE FROM”? Widzę, że działa tylko w tym przypadku, ale dlaczego?
LaBracca,

66
Myślę, ponieważ musisz wskazać, z której tabeli usunąć rekordy. Właśnie uruchomiłem zapytanie ze składnią, DELETE TableA, TableB ...które faktycznie usunęło odpowiednie rekordy z obu. Miły.
Andrew

1
W składni PostgreSQL z łączeniem nie działa, ale można użyć słowa kluczowego „using”. DELETE from TableA a using TableB b where b.Bid = a.Bid and [my filter condition]
bartolo-otrit

8
W MySQL pojawia się błąd „Nieznana tabela„ Tabela A ”w MULTI DELETE”, a to dlatego, że zadeklarowałeś alias dla tabeli A (a). Mała regulacja:DELETE a FROM TableA a INNER JOIN TableB b on b.Bid = a.Bid and [my filter condition]
masam

260

Użyłbym tej składni

Delete a 
from TableA a
Inner Join TableB b
on  a.BId = b.BId
WHERE [filter condition]

7
Ja też wolę tę składnię, wydaje się logicznie bardziej logiczne, co się dzieje. Wiem też, że możesz użyć tego samego typu składni dla UPDATE.
Adam Nofsinger

Ja też wolę to, ponieważ umieszczenie aliasu tabeli po USUŃ zawsze wydawało mi się bardziej intuicyjne, co do tego, co jest usuwane.
Jagd

14
Rzeczywiście jest to również preferowane dla mnie. Szczególnie w przypadkach, gdy muszę dołączyć do tej samej tabeli (np. W celu usunięcia zduplikowanych rekordów). W takim przypadku muszę użyć aliasu dla „strony”, z której usuwam, a ta składnia wyjaśnia, że ​​usuwam z podwójnego aliasu.
Chris Simmons,

29

Tak, możesz. Przykład:

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

8
Wolę odnieść się do tabeli w pierwszym wierszu według jej aliasu. To znaczy „Usuń a” zamiast „Usuń tabelę A”. W przypadku, gdy dołączasz do stołu ze sobą, jasne jest, którą stronę chcesz usunąć.
Jeremy Stein,

10

Próbowałem to zrobić z bazą danych dostępu i stwierdziłem, że muszę użyć pliku. * Zaraz po usunięciu.

DELETE a.*
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

Od odrzucone oczekiwaniu edit: „Nieruchomość UniqueRecords musi być ustawiony tak, inaczej to nie zadziała (. Support.microsoft.com/kb/240098 )”
StuperUser

8

W MySQL jest prawie tak samo , ale musisz użyć aliasu tabeli zaraz po słowie „DELETE”:

DELETE a
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

2

Powyższa składnia nie działa w Interbase 2007. Zamiast tego musiałem użyć czegoś takiego:

DELETE FROM TableA a WHERE [filter condition on TableA] 
  AND (a.BId IN (SELECT a.BId FROM TableB b JOIN TableA a 
                 ON a.BId = b.BId 
                 WHERE [filter condition on TableB]))

(Uwaga: Interbase nie obsługuje słowa kluczowego AS dla aliasów)


2

Używam tego

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]

a sposób @TheTXI jest wystarczający, ale czytam odpowiedzi i komentarze i znalazłem jedną rzecz, na którą należy odpowiedzieć, używając warunku w klauzuli WHERE lub jako warunku złączenia. Postanowiłem go przetestować i napisać fragment, ale nie znalazłem między nimi znaczącej różnicy. Tutaj możesz zobaczyć skrypt sql, a ważne jest to, że wolałem napisać go jako commnet, ponieważ nie jest to dokładna odpowiedź, ale jest duży i nie można go wstawić w komentarzach, proszę wybacz mi.

Declare @TableA  Table
(
  aId INT,
  aName VARCHAR(50),
  bId INT
)
Declare @TableB  Table
(
  bId INT,
  bName VARCHAR(50)  
)

Declare @TableC  Table
(
  cId INT,
  cName VARCHAR(50),
  dId INT
)
Declare @TableD  Table
(
  dId INT,
  dName VARCHAR(50)  
)

DECLARE @StartTime DATETIME;
SELECT @startTime = GETDATE();

DECLARE @i INT;

SET @i = 1;

WHILE @i < 1000000
BEGIN
  INSERT INTO @TableB VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableA VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE a
--SELECT *
FROM @TableA a
Inner Join @TableB b
ON  a.BId = b.BId
WHERE a.aName LIKE '%5'

SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())

SET @i = 1;
WHILE @i < 1000000
BEGIN
  INSERT INTO @TableD VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableC VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE c
--SELECT *
FROM @TableC c
Inner Join @TableD d
ON  c.DId = d.DId
AND c.cName LIKE '%5'

SELECT Duration    = DATEDIFF(ms,@StartTime,GETDATE())

Jeśli możesz uzyskać dobry powód z tego skryptu lub napisać inny użyteczny, proszę udostępnij. Dzięki i mam nadzieję, że ta pomoc.


1

Załóżmy, że masz 2 tabele, jedną z zestawem głównym (np. Pracownicy) i jedną z zestawem podrzędnym (np. Osoby zależne) i chcesz pozbyć się wszystkich wierszy danych w tabeli osób zależnych, których nie można zapisać z dowolnymi wierszami w tabeli wzorcowej.

delete from Dependents where EmpID in (
select d.EmpID from Employees e 
    right join Dependents d on e.EmpID = d.EmpID
    where e.EmpID is null)

Należy tutaj zauważyć, że najpierw zbierasz „tablicę” EmpID od sprzężenia, używając tego zestawu EmpID do wykonania operacji usuwania w tabeli Dependents.


1

W SQLite jedyne, co działa, to coś podobnego do odpowiedzi beauXjames.

Wydaje się, że sprowadza się to do tego DELETE FROM table1 WHERE table1.col1 IN (SOME TEMPORARY TABLE); i że niektóre tabele tymczasowe mogą być tworzone przez SELECT i DOŁĄCZ twoją tabelę, którą możesz filtrować tę tabelę tymczasową na podstawie warunku, że chcesz usunąć rekordy z Tabeli 1.


1

możesz uruchomić to zapytanie: -

Delete from TableA 
from 
TableA a, TableB b 
where a.Bid=b.Bid
AND [my filter condition]


1
DELETE FROM table1
where id IN 
    (SELECT id FROM table2..INNER JOIN..INNER JOIN WHERE etc)

Minimalizuj użycie zapytań DML z łączeniami. Powinieneś być w stanie wykonać większość wszystkich zapytań DML z podkwerendami jak wyżej.

Zasadniczo złączeń należy używać tylko wtedy, gdy trzeba WYBRAĆ lub GRUPOWAĆ według kolumn w 2 lub więcej tabelach. Jeśli dotykasz tylko wielu tabel, aby zdefiniować populację, użyj podkwerend. W przypadku zapytań DELETE użyj skorelowanych podkwerend.

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.