Jak można usunąć SQL za pomocą zapytania podrzędnego

Jeden z naszych programistów dodał następujący kod, aby usunąć zduplikowane rekordy z tabeli:

DELETE  SubQuery

FROM
(
    SELECT  ID
            ,FK1
            ,FK2
            ,CreatedDateTime
            ,ROW_NUMBER() OVER(PARTITION BY FK1, FK2 ORDER BY CreatedDateTime) AS RowNumber

    FROM    Table
)
AS SubQuery

WHERE   RowNumber > 1

Przeglądając kod, założyłem, że nie zadziała, jednak testowanie go w naszym środowisku testowym (SQL 2014) pokazuje, że działa!

Skąd SQL wie, aby rozwiązać zapytanie podrzędne i usunąć rekordy table?

subqueryMasz w kodzie nazywa się tabela pochodzi . To nie jest tabela podstawowa, ale tabela, która „żyje” podczas uruchamiania zapytania. Podobnie jak widoki (zwane również tabelami przeglądanymi ) - aw ostatnich wersjach CTE, który jest innym, czwartym sposobem „zdefiniowania” tabeli wewnątrz zapytania - są one podobne do tabeli na wiele sposobów. Można selectz nich, można ich używać w fromlub na joinnich do innych tabel bazowych (lub nie!).

W niektórych DBMS (nie wszystkie DBMS zaimplementowały to w ten sam sposób) te tabele / widoki można aktualizować . I „które można aktualizować” oznacza, że możemy również update, insertdo lubdelete od nich.

Istnieją jednak ograniczenia i jest to oczekiwane. Wyobraź sobie, że subquerybyło to połączenie 2 (lub 17 stołów). Co by to deleteznaczyło? (z których tabel należy usunąć wiersze?) Widoki, które można aktualizować, to bardzo skomplikowana sprawa . Jest ostatnia (2012) książka, całkowicie na ten temat, napisana przez Chrisa Date, znanego eksperta w teorii relacji: Zobacz aktualizację i teorię relacyjną .

Gdy tabela pochodna (lub widok) jest bardzo prostym zapytaniem, tak jak ma tylko jedną tabelę podstawową (ewentualnie ograniczoną przez a WHERE) i nie GROUP BY, wtedy każdy wiersz tabeli pochodnej odpowiada jednemu wierszowi w podstawowej tabeli bazowej, więc jest łatwe ^* aktualizacja, wstawianie lub usuwanie z tego.

Kiedy kod wewnątrz podzapytania jest bardziej złożony, zależy to od tego, czy wiersze pochodnej tabeli / widoku można prześledzić / rozstrzygnąć w wiersze z jednej z podstawowych tabel podstawowych.

SQL Server, można przeczytać więcej w Aktualizowalne Widoki ustępu w MSDN: CREATE VIEW.

Aktualizowalne widoki

Możesz modyfikować dane podstawowej tabeli bazowej poprzez widok, o ile spełnione są następujące warunki:

• Wszelkie modyfikacje, włącznie UPDATE, INSERToraz DELETEoświadczenia, kolumny odniesienia musi z tylko jednej tabeli podstawowej.

• Zmodyfikowane kolumny w widoku muszą bezpośrednio odwoływać się do podstawowych danych w kolumnach tabeli. Kolumn nie można wyprowadzić w żaden inny sposób, na przykład przez:

• Agregat funkcja: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, i VARP.

• Obliczenia. Kolumny nie można obliczyć z wyrażenia korzystającego z innych kolumn. Kolumny, które są tworzone przy użyciu zestaw operatorów UNION, UNION ALL, CROSSJOIN, EXCEPToraz INTERSECT kwotę do obliczeń i nie są również uaktualniać.

• Kolumny modyfikowane nie są dotknięte GROUP BY, HAVINGczy DISTINCTklauzule.

• TOPnie jest używany nigdzie w select_statement widoku wraz z WITH CHECK OPTIONklauzulą.

Poprzednie ograniczenia dotyczą dowolnych podzapytań w FROMklauzuli widoku, podobnie jak dotyczą samego widoku. Zasadniczo aparat bazy danych musi być w stanie jednoznacznie śledzić zmiany z definicji widoku do jednej tabeli podstawowej.

^{W rzeczywistości deletejest łatwiejsze, mniej skomplikowane niż update. SQL Server potrzebuje tylko kluczy podstawowych lub innego sposobu identyfikacji, które wiersze tabeli podstawowej mają zostać usunięte. Ponieważ updateistnieje dodatkowe (raczej oczywiste) ograniczenie, że nie możemy zaktualizować kolumny obliczeniowej. Możesz spróbować zmodyfikować zapytanie, aby wykonać aktualizację. Aktualizacja CreatedDateTimeprawdopodobnie będzie działać dobrze, ale próba aktualizacji RowNumberkolumny obliczeniowej spowoduje błąd. I insertjest jeszcze bardziej złożony, ponieważ musielibyśmy podać wartości dla wszystkich kolumn tabeli podstawowej, które nie mają DEFAULTograniczenia.}

Łatwo jest zobaczyć, kiedy spojrzysz na plan zapytań. W twoim przypadku plan zawiera tylko dodatkowego operatora projektu segmentu i sekwencji do obsługi numeru wiersza. Ten typ operacji działa tylko wtedy, gdy SQL Server faktycznie może rozwiązać tabelę bazową.

Usuwanie podkwerend i CTE jest w pełni obsługiwane i bardzo wydajne, szczególnie w przypadku usuwania duplikatów. Wydaje mi się również, że pamiętam używanie go w starszych wersjach SQL Server.

Więcej w moim starym blogu .