Wprowadzenie do instrukcji Delete

Konstrukcja DELETE jest używana do usuwania wierszy z tabeli danych SQL Server. W tym artykule dowiemy się, jak używać instrukcji DELETE. Omówimy najlepsze praktyki, ograniczenia i kilka przykładów.

Jest to czwarty artykuł z serii artykułów. Możesz zacząć od początku, czytając Wprowadzenie do SQL Server Data Modification Statements.

Wszystkie przykłady dla tej lekcji są oparte na Microsoft SQL Server Management Studio i bazie danych AdventureWorks2012. Możesz zacząć używać tych darmowych narzędzi korzystając z mojego przewodnika Getting Started Using SQL Server

Before we Begin

Chociaż ten artykuł używa bazy danych AdventureWorks do swoich przykładów, zdecydowałem się stworzyć przykładową tabelę do użycia wewnątrz bazy danych, aby pomóc lepiej zilustrować przykłady. Możesz znaleźć skrypt, który będziesz musiał uruchomić tutaj.

Wstępnie wypełnijmy tabelę danymi za pomocą poniższego polecenia INSERT:

WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)AS (SELECT S.FirstName + ' ' + S.LastName, S.SalesLastYear, S.City ,NEWID()FROM Sales.vSalesPerson SWHERE S.SalesLastYear > 1000000)INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName, SalesLastYear, City, rowguid FROM topSalesPerson

Możesz dowiedzieć się więcej o poleceniu INSERT czytając nasz artykuł Wprowadzenie do polecenia INSERT.

Podstawowa struktura instrukcji DELETE

Konstrukcja instrukcji DELETE jest używana do zmiany wartości kolumn.

Istnieją trzy elementy instrukcji DELETE:

1. Tabela, z której chcesz usunąć wiersze.
2. Kryteria używane do wyboru wierszy do usunięcia.

Ogólny format instrukcji DELECT to:

DELETEFROM tableNameWHERE searchCondition…

Zrobimy teraz kilka przykładowych DELETE, więc jeśli jeszcze tego nie zrobiłeś, uruchom skrypt, aby utworzyć tabelę esqlSalesPerson.

Przykład – Usuwanie każdego wiersza

Kiedy używasz DELETE bez klauzuli WHERE, usuwa on każdy wiersz z tabeli. Jeśli chcemy usunąć każdy wiersz z esqlSalesPerson moglibyśmy uruchomić:

DELETEFROM esqlSalesPerson

Zalecałbym, dla celów naszego przykładu, zawinąć polecenie DELETE w transakcję, aby nie musieć wielokrotnie wstawiać wierszy do przykładowej bazy danych. Tutaj zobaczysz tę samą instrukcję DELETE.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonSELECT COUNT(1) FROM esqlSalesPersonROLLBACK

Pierwsza instrukcja count zwraca 13 wierszy; podczas gdy druga zwraca 0. Ponieważ ROLLBACK transakcja, operacja usuwania nie jest trwała.

Prostszy przykład – usuwanie pojedynczego wiersza

Bardziej realistycznym przypadkiem użycia jest usuwanie pojedynczego wiersza. W wielu aplikacjach można to osiągnąć poprzez filtrowanie na pojedynczym wierszu.

Jeśli znasz klucz główny, jesteś złoty, ponieważ klucz główny jest unikalny i ma za zadanie pozytywnie zidentyfikować każdy wiersz.

Załóżmy, że chcemy usunąć wiersz Jillian Carson. Aby to zrobić, wykonalibyśmy następujące polecenie:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Możesz się zastanawiać, skąd znasz klucz główny.

Możesz sobie wyobrazić, że masz aplikację internetową, która wymienia wszystkich sprzedawców, że siatka może zawierać pełne imię i nazwisko sprzedawcy oraz sprzedaż z ostatniego roku, ale ukryty w siatce byłby również jego SalesPersonID.

Gdy użytkownik wybrał wiersz na siatce i zdecydował się usunąć wiersz, aplikacja pobrałaby ukryty klucz główny dla wiersza, a następnie wydałaby polecenie usunięcia do bazy danych.

Prostszy przykład – usuwanie wielu wierszy

Załóżmy, że chcemy pokazać tylko sprzedawców o wysokiej wydajności w tabeli esqlSalesPerson. Chcemy zachować tylko tych sprzedawców, których zeszłoroczna sprzedaż była większa lub równa 2 000 000 $.

Ponieważ nasza tabela zawiera osoby o sprzedaży mniejszej niż ta kwota, musimy je usunąć, wykonując następujące polecenie:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Możesz uruchomić następujące polecenie, aby wypróbować skrypt w ramach transakcji:

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Oto wynik działania skryptu:

Niebieska strzałka pokazuje, że pierwotnie było 13 rekordów, a czerwona pozostałe, których sprzedaż jest większa lub równa dwóm milionom dolarów.

Można również tworzyć bardziej złożone warunki filtrowania. W dalszej części artykułu pokażemy, jak używać podzapytań. Możesz również użyć warunków logicznych w klauzuli WHERE, tak samo jak w przypadku instrukcji SELECT.

Uwagi dotyczące użycia instrukcji DELETE

Aby usunąć wszystkie wiersze w tabeli, użyj TRUNCATE TABLE. Jest to znacznie szybsze niż DELETE, ponieważ rejestruje zmiany. Istnieją jednak kluczowe różnice (Zobacz DELETE isn’t TRUNCATE! poniżej)

Nie zapomnij również, że możesz użyć funkcji @@ROWCOUNT, aby dowiedzieć się ile wierszy zostało usuniętych.

Obsługa błędów

Jeśli instrukcja DELETE rzuci błąd wszystkie wiersze są przywracane do stanu przed wykonaniem instrukcji. Jeśli błąd zostanie wywołany, żadne wiersze nie zostaną usunięte.

Istnieje wiele powodów, dla których instrukcja delete może się nie powieść. Niektóre z nich to:

• Orphan Rows – jeśli ograniczenie FOREIGN KEY jest zdefiniowane między dwoma tabelami, takimi jak parentTable i childTable, to usunięcie wiersza parentTable spowoduje błąd, jeśli istnieją wiersze childTable związane z rodzicem. Sposobem na obejście tego problemu jest najpierw usunięcie odpowiednich wierszy childTable, a następnie wiersza parentTable.
• Błędy arytmetyczne – Jeśli wyrażenie skutkuje błędem arytmetycznym, takim jak dzielenie przez zero, DELETE jest anulowane i żadne wiersze nie są usuwane.

Blokowanie

Zachowanie

Komenda delete nakłada wyłączną (X) blokadę na tabelę. Oznacza to, że żadne inne zapytanie nie może modyfikować danych tabeli do momentu zakończenia transakcji DELETE.

Możesz nadal czytać dane, ale musisz użyć podpowiedzi NOLOCK lub poziomu izolacji read uncommitted.

DELETE to nie TRUNCATE!

Delete to nie TRUNCATE! Użyj DELETE aby usunąć jeden lub więcej wierszy z tabeli. Tylko w wyjątkowych sytuacjach, takich jak konieczność przywrócenia tabeli do stanu początkowego, powinieneś rozważyć TRUNCATE.

Wiele osób miesza DELETE i TRUNCATE. Jeśli nie masz pewności, polecam przeczytać mój post na temat Jaka jest różnica między Truncate i Delete w SQL Server?

Przykład złożony – DELETE Using a SubQuery

Możesz również tworzyć bardziej złożone instrukcje usuwania. Prawie każda klauzula, którą można zapisać w klauzuli WHERE instrukcji SELECT, może być zapisana w instrukcji DELETE, włączając w to podzapytania w klauzuli WHERE.

Zróbmy przykład.

Załóżmy, że musisz usunąć wszystkich sprzedawców, którzy mieszkają w USA z tabeli esqlSalesPerson. Chociaż nasza tabela ma miasto, nie ma kraju. Możemy to obejść używając podzapytania.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Uwaga, zawinąłem ten przykład w transakcję, aby nie usunąć trwale moich danych testowych.

Podpytanie jest pokolorowane na niebiesko. Zauważ, że nie różni się ono od żadnego innego skorelowanego podzapytania, którego używałeś w przeszłości.

Mechanika tego polecenia DELETE jest następująca:

1. Znajdź różne miasta dla wszystkich ludzi, którzy mieszkają w USA.
2. DELETE sprzedawców, których miasto znajduje się na tej liście.

Pierwszy krok jest wynikiem podzapytania. Drugi krok usuwa wiersze zgodnie z klauzulą WHERE.