Einführung in die Delete-Anweisung

Die DELETE-Anweisung wird verwendet, um Zeilen aus einer SQL Server-Datentabelle zu entfernen. In diesem Artikel erfahren Sie, wie Sie die DELETE-Anweisung verwenden können. Wir besprechen einige Best Practices, Einschränkungen und schließen mit mehreren Beispielen ab.

Dies ist der vierte Artikel in einer Reihe von Artikeln. Sie können am Anfang beginnen, indem Sie Einführung in SQL Server-Datenänderungsanweisungen lesen.

Alle Beispiele in dieser Lektion basieren auf Microsoft SQL Server Management Studio und der AdventureWorks2012-Datenbank. Sie können mit diesen kostenlosen Tools beginnen, indem Sie meinen Leitfaden Getting Started Using SQL Server

Before we Begin

Obwohl dieser Artikel die AdventureWorks-Datenbank für seine Beispiele verwendet, habe ich beschlossen, eine Beispieltabelle für die Verwendung innerhalb der Datenbank zu erstellen, um die Beispiele besser zu veranschaulichen. Das Skript, das Sie zur Ausführung benötigen, finden Sie hier.

Füllen wir die Tabelle zunächst mit einigen Daten auf, indem wir die folgende INSERT-Anweisung verwenden:

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

Weitere Informationen zur INSERT-Anweisung finden Sie in unserem Artikel Einführung in die INSERT-Anweisung.

Grundlegender Aufbau der DELETE-Anweisung

Die DELETE-Anweisung wird verwendet, um Spaltenwerte zu ändern.

Eine DELETE-Anweisung besteht aus drei Komponenten:

1. Die Tabelle, aus der Sie Zeilen entfernen möchten.
2. Die Kriterien, nach denen die zu entfernenden Zeilen ausgewählt werden.

Das allgemeine Format für die DELECT-Anweisung lautet:

DELETEFROM tableNameWHERE searchCondition…

Wir werden nun einige DELETE-Beispiele durchführen. Wenn Sie dies noch nicht getan haben, führen Sie das Skript aus, um die Tabelle esqlSalesPerson zu erstellen.

Einfaches Beispiel – Löschen jeder Zeile

Wenn Sie DELETE ohne WHERE-Klausel verwenden, wird jede Zeile aus der Tabelle entfernt. Wenn wir alle Zeilen aus esqlSalesPerson löschen wollen, könnten wir Folgendes ausführen:

DELETEFROM esqlSalesPerson

Für die Zwecke unseres Beispiels würde ich empfehlen, den DELETE-Befehl in eine Transaktion zu verpacken, damit Sie nicht wiederholt Zeilen in die Beispieldatenbank einfügen müssen. Hier sehen Sie dieselbe DELETE-Anweisung.

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

Die erste Zählanweisung gibt 13 Zeilen zurück, die zweite dagegen 0. Da ich die Transaktion ROLLBACK mache, ist der Löschvorgang nicht dauerhaft.

Ein einfaches Beispiel – Löschen einer einzelnen Zeile

Ein realistischerer Anwendungsfall ist das Löschen einer einzelnen Zeile. In vielen Anwendungen wird dies durch das Filtern einer einzelnen Zeile erreicht.

Wenn Sie den Primärschlüssel kennen, haben Sie es geschafft, denn der Primärschlüssel ist eindeutig und soll jede Zeile eindeutig identifizieren.

Angenommen, wir wollen die Zeile von Jillian Carson löschen. Dazu würden wir Folgendes eingeben:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Sie fragen sich vielleicht, woher Sie den Primärschlüssel kennen.

Sie können sich vorstellen, dass bei einer Webanwendung, die alle Vertriebsmitarbeiter auflistet, das Raster zwar den vollständigen Namen des Vertriebsmitarbeiters und den Umsatz des letzten Jahres enthält, aber im Raster auch seine SalesPersonID versteckt ist.

Wenn ein Benutzer eine Zeile im Raster auswählt und die Zeile entfernen möchte, würde die Anwendung den verborgenen Primärschlüssel für die Zeile abrufen und dann den Löschbefehl an die Datenbank senden.

Einfaches Beispiel – Löschen mehrerer Zeilen

Angenommen, wir möchten nur leistungsstarke Vertriebsmitarbeiter in der Tabelle esqlSalesPerson anzeigen. Wir möchten nur die Vertriebsmitarbeiter behalten, deren Vorjahresumsatz größer oder gleich 2.000.000 $ ist.

Da unsere Tabelle die Personen mit weniger als diesem Betrag enthält, müssen wir sie entfernen, indem wir Folgendes ausführen:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Sie können den folgenden Befehl ausführen, um das Skript innerhalb einer Transaktion zu testen:

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

Hier sind die Ergebnisse der Ausführung des Skripts:

Der blaue Pfeil zeigt, dass es ursprünglich 13 Datensätze gab, und der rote die verbleibenden, deren Umsatz größer oder gleich zwei Millionen Dollar ist.

Sie können auch komplexere Filterbedingungen erstellen. Später in diesem Artikel wird gezeigt, wie man eine Unterabfrage verwendet. Sie können auch boolesche Bedingungen in der WHERE-Klausel verwenden, genau wie bei der SELECT-Anweisung.

Überlegungen zur Verwendung der DELETE-Anweisung

Um alle Zeilen in einer Tabelle zu löschen, verwenden Sie TRUNCATE TABLE. Es ist viel schneller als DELETE, da es Änderungen protokolliert. Aber es gibt wichtige Unterschiede (siehe DELETE ist nicht TRUNCATE! unten)

Außerdem können Sie die Funktion @@ROWCOUNT verwenden, um herauszufinden, wie viele Zeilen gelöscht wurden.

Fehlerbehandlung

Wenn eine DELETE-Anweisung einen Fehler auslöst, werden alle Zeilen in den Zustand vor der Ausführung der Anweisung zurückversetzt. Wenn ein Fehler auftritt, werden keine Zeilen entfernt.

Es gibt viele Gründe, warum eine Löschanweisung fehlschlagen kann. Einige der typischsten sind:

• Waise Zeilen – wenn eine FOREIGN KEY-Beschränkung zwischen zwei Tabellen definiert ist, wie z.B. parentTable und childTable, dann führt DELETE einer parentTable-Zeile zu einem Fehler, wenn childTable-Zeilen mit Bezug auf die parent existieren. Dies kann umgangen werden, indem zuerst die entsprechenden ChildTable-Zeilen und dann die ParentTable-Zeile entfernt werden.
• Arithmetische Fehler – Wenn eine Ausdrucksauswertung zu einem arithmetischen Fehler führt, wie z.B. Dividieren durch Null, wird das DELETE abgebrochen und es werden keine Zeilen entfernt.

Locking-Verhalten

Eine Löschanweisung setzt eine exklusive (X) Sperre auf die Tabelle. Das bedeutet, dass keine andere Abfrage die Daten der Tabelle ändern kann, bis die DELETE-Transaktion abgeschlossen ist.

Sie können die Daten immer noch lesen, müssen aber den NOLOCK-Hinweis oder die Isolationsebene „Read uncommitted“ verwenden.

DELETE ist nicht TRUNCATE!

Delete ist nicht TRUNCATE! Verwenden Sie DELETE, um eine oder mehrere Zeilen aus einer Tabelle zu entfernen. Nur in besonderen Situationen, z.B. wenn Sie eine Tabelle auf ihren Ausgangszustand zurücksetzen müssen, sollten Sie TRUNCATE in Betracht ziehen.

Viele Leute verwechseln DELETE und TRUNCATE. Wenn Sie unsicher sind, empfehle ich Ihnen, meinen Beitrag Was ist der Unterschied zwischen Truncate und Delete in SQL Server?

Komplexes Beispiel – DELETE mit einer SubQuery

Sie können auch komplexere Löschanweisungen erstellen. Fast jede Klausel, die Sie in eine SELECT-Anweisung WHERE-Klausel schreiben können, kann in die DELETE-Anweisung geschrieben werden, einschließlich Unterabfragen in der WHERE-Klausel.

Lassen Sie uns ein Beispiel machen.

Angenommen, Sie müssen alle Verkäufer, die in den USA leben, aus der Tabelle esqlSalesPerson entfernen. Unsere Tabelle enthält zwar den Ort, aber nicht das Land. Wir können dies mit einer Unterabfrage umgehen.

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

Bitte beachten Sie, dass ich das Beispiel in eine Transaktion verpackt habe, damit meine Testdaten nicht dauerhaft gelöscht werden.

Die Unterabfrage ist blau gefärbt. Beachten Sie, dass sie sich nicht von anderen korrelierten Unterabfragen unterscheidet, die Sie in der Vergangenheit verwendet haben.

Die Mechanismen dieser DELETE-Anweisung sind:

1. Finde eindeutige Städte für alle Personen, die in den USA leben.
2. Lösche Vertriebsmitarbeiter, deren Stadt in dieser Liste steht.

Der erste Schritt ergibt sich aus der Unterabfrage. Der zweite Schritt löscht Zeilen gemäß der WHERE-Klausel.