DELETE (Transact-SQL)

Entfernt eine oder mehrere Zeilen aus einer Tabelle oder Sicht in SQL Server 2008.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen


[ WITH common_table_expression [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ OUTPUT Clause ]
    [ FROMtable_source [ ,...n ] ] 
    [ WHERE { search_condition 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION (Query Hint [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

WITH common_table_expression

Gibt das auch als allgemeiner Tabellenausdruck bezeichnete temporäre, benannte Resultset an, das im Rahmen der DELETE-Anweisung definiert wurde. Das Resultset wird von einer SELECT-Anweisung abgeleitet. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

TOP ( expression) [ PERCENT ]

Gibt die Anzahl oder den Prozentsatz zufälliger Zeilen an, die gelöscht werden. expression kann entweder eine Anzahl oder ein Prozentsatz der Zeilen sein. Die Zeilen, auf die in einem mit DELETE verwendeten TOP-Ausdruck verwiesen wird, sind nicht in einer bestimmten Reihenfolge angeordnet.

In den Anweisungen INSERT, UPDATE, MERGE und DELETE sind Klammern erforderlich, die expression in TOP begrenzen. Weitere Informationen finden Sie unter TOP (Transact-SQL).

FROM

Ein optionales Schlüsselwort, das zwischen dem DELETE-Schlüsselwort und dem Zielobjekt verwendet werden kann.

table_alias

Der in der FROMtable_source-Klausel angegebene Alias, der die Tabelle oder Sicht darstellt, aus der die Zeilen gelöscht werden sollen.

server_name

Der Name des Verbindungsservers, auf dem sich die Tabelle oder Sicht befindet. server_name kann als Name eines Verbindungsservers oder mithilfe der OPENDATASOURCE-Funktion angegeben werden.

Wenn server_name als Verbindungsserver angegeben wird, sind database_name und schema_name erforderlich. Wenn server_name mit OPENDATASOURCE angegeben wird, gelten database_name und schema_name möglicherweise nicht für alle Datenquellen und unterliegen den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

database_name

Der Name der Datenbank.

schema_name

Der Name des Schemas, dem die Tabelle oder Sicht angehört.

table_or view_name

Der Name der Tabelle oder Sicht, aus der die Zeilen entfernt werden sollen.

Die Sicht, auf die table_or_view_name verweist, muss aktualisierbar sein und auf genau eine Basistabelle in der FROM-Klausel der Sicht verweisen. Weitere Informationen zu aktualisierbaren Sichten finden Sie unter CREATE VIEW (Transact-SQL).

rowset_function_limited

Die Funktion OPENQUERY oder OPENROWSET, die der Funktionalität des Anbieters unterliegt. Weitere Informationen zur Funktionalität, die für den Anbieter erforderlich ist, finden Sie unter UPDATE- und DELETE-Anforderungen für OLE DB-Anbieter.

WITH (table_hint_limited [... n] )

Gibt mindestens einen Tabellenhinweis an, der für eine Zieltabelle zulässig ist. Das WITH-Schlüsselwort und die Klammern sind erforderlich. NOLOCK und READUNCOMMITTED sind nicht zulässig. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).

@table_variable

Gibt eine Tabelle-Variable an.

<OUTPUT_Clause>

Gibt gelöschte Zeilen bzw. auf diesen basierende Ausdrücke als Teil des Löschvorgangs zurück. Die OUTPUT-Klausel wird in DML-Anweisungen, die auf lokale partitionierte Sichten, verteilte partitionierte Sichten, Remotetabellen oder Remotesichten ausgerichtet sind, nicht unterstützt. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

FROM table_source

Gibt eine zusätzliche FROM-Klausel an, mit der table_or view_name des Ziels mit <table_source> verknüpft werden kann, um die zu entfernenden Zeilen zu identifizieren. Diese Transact-SQL-Erweiterung für DELETE kann anstelle einer Unterabfrage in der WHERE-Klausel verwendet werden.

Weitere Informationen finden Sie unter FROM (Transact-SQL).

WHERE

Gibt die Bedingungen an, die zur Beschränkung der Anzahl der zu löschenden Zeilen verwendet werden. Wird keine WHERE-Klausel angegeben, werden mit DELETE alle Zeilen aus der Tabelle entfernt.

Es gibt zwei Formen von Löschoperationen, die darauf basieren, was in der WHERE-Klausel angegeben wird:

  • Gesuchte Löschungen geben eine Suchbedingung an, um die zu löschenden Zeilen zu kennzeichnen. Beispiel: WHERE column_name = value.

  • Positionierte Löschungen verwenden die CURRENT OF-Klausel, um einen Cursor anzugeben. Die Löschoperation wird an der aktuellen Position des Cursors ausgeführt. Dies kann genauer sein als eine gesuchte DELETE-Anweisung, die eine WHERE search_condition-Klausel zur Qualifizierung der zu löschenden Zeilen verwendet. Eine gesuchte DELETE-Anweisung löscht mehrere Zeilen, wenn die Suchbedingung nicht eindeutig eine einzelne Zeile identifiziert.

search_condition

Gibt die Einschränkungsbedingungen für die zu löschenden Zeilen an. Es gibt keinen Höchstwert hinsichtlich der Anzahl der Prädikate in einer Suchbedingung. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL).

CURRENT OF

Gibt an, dass DELETE an der aktuellen Position des angegebenen Cursors durchgeführt wird.

GLOBAL

Gibt an, dass cursor_name auf einen globalen Cursor verweist.

cursor_name

Der Name des geöffneten Cursors, von dem der Abruf erfolgt. Wenn sowohl ein globaler als auch ein lokaler Cursor namens cursor_name vorhanden ist, bezieht sich dieses Argument auf den globalen Cursor, wenn GLOBAL angegeben ist. Andernfalls bezieht es sich auf den lokalen Cursor. Der Cursor muss Aktualisierungen zulassen.

cursor_variable_name

Der Name einer Cursorvariablen. Die Cursorvariable muss auf einen Cursor verweisen, der Aktualisierungen zulässt.

OPTION (query_hint [ ,... n] )

Schlüsselwörter, die angeben, dass Hinweise für den Optimierer verwendet werden, um die Verarbeitung der Anweisung durch Database Engine (Datenbankmodul) anzupassen. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

Verwenden Sie TRUNCATE TABLE, um alle Zeilen in einer Tabelle zu löschen. TRUNCATE TABLE ist schneller als DELETE und verwendet weniger System- und Transaktionsprotokollierungsressourcen.

Verwenden Sie die @@ROWCOUNT-Funktion, um die Anzahl der gelöschten Zeilen an die Clientanwendung zurückzugeben. Weitere Informationen finden Sie unter @@ROWCOUNT (Transact-SQL).

Das Verwenden von SET ROWCOUNT wird in der nächsten Version von SQL Server keine Auswirkungen auf DELETE-Anweisungen haben. Verwenden Sie SET ROWCOUNT bei neuen Entwicklungsarbeiten nicht zusammen mit DELETE-Anweisungen, und ändern Sie Anwendungen, die SET ROWCOUNT derzeit verwenden, sodass sie die TOP-Syntax verwenden.

Sie können die Fehlerbehandlung für die DELETE-Anweisung durch Angeben der Anweisung in einem TRY…CATCH-Konstrukt implementieren. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

Die DELETE-Anweisung erzeugt möglicherweise einen Fehler, wenn sie gegen einen Trigger verstößt oder versucht, eine Zeile zu entfernen, auf die von Daten einer anderen Tabelle mit einer FOREIGN KEY-Einschränkung verwiesen wird. Entfernt die DELETE-Anweisung mehrere Zeilen und verstößt eine der entfernten Zeilen gegen einen Trigger oder eine Einschränkung, wird die Anweisung abgebrochen, ein Fehler gemeldet und keine Zeilen entfernt.

Wenn in einer DELETE-Anweisung bei der Auswertung eines Ausdrucks ein arithmetischer Fehler (Überlauf, Division durch null oder Domänenfehler) auftritt, behandelt Database Engine (Datenbankmodul) den Fehler so, als wäre SET ARITHABORT auf ON festgelegt. Der Rest des Batches wird abgebrochen, und eine Fehlermeldung wird zurückgegeben.

DELETE kann im Textkörper einer benutzerdefinierten Funktion verwendet werden, wenn es sich bei dem geänderten Objekt um eine Tabellenvariable handelt.

Wenn Sie eine Zeile mit einer FILESTREAM-Spalte löschen, löschen Sie auch die zugrunde liegenden Dateisystemdateien. Die zugrunde liegenden Dateien werden vom FILESTREAM Garbage Collector entfernt. Weitere Informationen finden Sie unter Verwalten von FILESTREAM-Daten mit Transact-SQL.

Die FROM-Klausel kann nicht für eine DELETE-Anweisung angegeben werden, die entweder direkt oder indirekt auf eine Sicht verweist, für die ein INSTEAD OF-Trigger definiert wurde. Weitere Informationen zu INSTEAD OF-Triggern finden Sie unter CREATE TRIGGER (Transact-SQL).

Wenn TOP mit DELETE verwendet wird, werden die Zeilen, auf die verwiesen wird, nicht in einer bestimmten Reihenfolge angeordnet, und die ORDER BY-KLausel kann in dieser Anweisung nicht direkt angegeben werden. Wenn Sie die TOP-Klausel verwenden müssen, um Zeilen in einer sinnvollen Reihenfolge zu löschen, müssen Sie sie zusammen mit einer ORDER BY-Klausel in einer untergeordneten SELECT-Anweisung verwenden. Informationen hierzu finden Sie im Abschnitt mit Beispielen nach diesem Thema.

TOP darf nicht in einer DELETE-Anweisung für partitionierte Sichten verwendet werden.

Die Einstellung der Option SET ROWCOUNT wird für DELETE-Anweisungen ignoriert, die für Remotetabellen oder für lokale oder verteilte partitionierte Sichten ausgeführt werden.

Wenn Zeilen aus einem Heap gelöscht werden, können von Database Engine (Datenbankmodul) Zeilen- oder Seitensperren für den Vorgang verwendet werden. Demzufolge bleiben die durch den Löschvorgang geleerten Seiten dem Heap zugeordnet. Wenn die Zuordnung leerer Seiten nicht aufgehoben wird, kann der zugehörige Speicherplatz nicht für andere Objekte in der Datenbank verwendet werden. Verwenden Sie eine der folgenden Methoden, um Zeilen in einem Heap zu löschen und die Zuordnung der Seiten aufzuheben:

  • Geben Sie den TABLOCK-Hinweis in der DELETE-Anweisung an. Mithilfe des TABLOCK-Hinweises wird durch den Löschvorgang eine freigegebene Sperre für die Tabelle statt einer Zeilen- oder Seitensperre angefordert. Dadurch ist es möglich, die Zuordnung der Seiten aufzuheben. Weitere Informationen zum TABLOCK-Hinweis finden Sie unter Tabellenhinweise (Transact-SQL).

  • Verwenden Sie TRUNCATE TABLE, wenn alle Zeilen aus der Tabelle gelöscht werden sollen.

  • Erstellen Sie einen gruppierten Index für den Heap, bevor Sie die Zeilen löschen. Nach dem Löschen der Zeilen können Sie den gruppierten Index löschen. Diese Methode ist zeitaufwändiger als die vorherigen Methoden und beansprucht mehr temporäre Ressourcen.

Weitere Informationen zu Sperren finden Sie unter Sperren im Datenbankmodul.

Die DELETE-Anweisung wird immer vollständig protokolliert.

Für die Zieltabelle sind DELETE-Berechtigungen erforderlich. SELECT-Berechtigungen werden ebenfalls benötigt, wenn die Anweisung eine WHERE-Klausel enthält.

DELETE-Berechtigungen werden standardmäßig Mitgliedern der festen Serverrolle sysadmin und der festen Datenbankrollen db_owner und db_datawriter sowie Tabellenbesitzern erteilt. Mitglieder der Rollen sysadmin, db_owner und db_securityadmin sowie der Tabellenbesitzer können Berechtigungen an andere Benutzer übertragen.

Kategorie

Featuresyntaxelemente

Grundlegende Syntax

DELETE

Angeben der zu löschenden Zeilen

WHERE-Klausel • TOP • FROM-Klausel und Unterabfragen • Cursor • WITH (allgemeiner Tabellenausdruck)

Angeben von Zielobjekten, die keine Standardtabellen sind

Sichten • Tabellenvariablen

Löschen von Zeilen in einer Remotetabelle

Verbindungsserver • OPENQUERY-Rowsetfunktion • OPENDATASOURCE-Rowsetfunktion

Außerkraftsetzen des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen

Tabellenhinweise

Erfassen der Ergebnisse der DELETE-Anweisung

OUTPUT-Klausel

Verwenden von DELETE in anderen Anweisungen

Gespeicherte Prozedur • MERGE

Anhand von Beispielen in diesem Abschnitt wird die grundlegende Funktion der DELETE-Anweisung mithilfe der mindestens erforderlichen Syntax veranschaulicht.

Im folgenden Beispiel werden alle Zeilen aus der SalesPersonQuotaHistory-Tabelle gelöscht, da keine WHERE-Klausel verwendet wird, um die Anzahl der gelöschten Zeilen zu begrenzen.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO


Die Beispiele in diesem Abschnitt zeigen Möglichkeiten, die Anzahl der gelöschten Zeilen zu begrenzen.

A. Verwenden der WHERE-Klausel zum Begrenzen von Zeilen

Im folgenden Beispiel werden alle Zeilen der ProductCostHistory-Tabelle gelöscht, bei denen der Wert in der StandardCost-Spalte 1000.00 überschreitet.

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO


B. Verwenden der TOP-Klausel

Mithilfe der TOP-Klausel können Sie die Anzahl der Zeilen beschränken, die in einer DELETE-Anweisung gelöscht werden. Wird eine TOP (n)-Klausel zusammen mit DELETE verwendet, wird der Löschvorgang für eine zufällige Auswahl von n Zeilen ausgeführt.

Im folgenden Beispiel werden 2.5 % der Zeilen (27 Zeilen) in der ProductInventory-Tabelle gelöscht.

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO


Im folgenden Beispiel werden 20 zufällige Zeilen mit Fälligkeitsdaten vor dem 1. Juli 2002 aus der PurchaseOrderDetail-Tabelle gelöscht.

USE AdventureWorks;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO


Wenn Sie die TOP-Klausel verwenden müssen, um Zeilen in einer sinnvollen Reihenfolge zu löschen, müssen Sie sie zusammen mit ORDER BY in einer untergeordneten SELECT-Anweisung verwenden. Im folgenden Beispiel werden die zehn Zeilen der PurchaseOrderDetail-Tabelle mit den frühesten Fälligkeitsdaten gelöscht. Die in der untergeordneten SELECT-Anweisung angegebene Spalte (PurchaseOrderID) ist der Primärschlüssel der Tabelle, um sicherzustellen, dass nur 10 Zeilen gelöscht werden. Wird in der untergeordneten SELECT-Anweisung eine Nichtschlüsselspalte verwendet, werden möglicherweise mehr als 10 Zeilen gelöscht, wenn die angegebene Spalte doppelte Werte enthält.

USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO


C. Verwenden eines Cursors zum Löschen der aktuellen Zeile

Im folgenden Beispiel wird eine einzelne Zeile der EmployeePayHistory-Tabelle gelöscht, bei der ein Cursor mit dem Namen complex_cursor verwendet wird. Von der Löschoperation ist nur die Zeile betroffen, die aktuell durch den Cursor abgerufen wird.

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO


D. Verwenden einer Unterabfrage und der Transact-SQL-FROM-Erweiterung

Im folgenden Beispiel wird die Transact-SQL-Erweiterung verwendet, um basierend auf einer Verknüpfung oder einer abhängigen Unterabfrage Datensätze aus einer Basistabelle zu löschen. Die erste DELETE-Anweisung zeigt die Lösung mit einer ISO-kompatiblen Unterabfrage, die zweite DELETE-Anweisung zeigt die Transact-SQL-Erweiterung an. Beide Abfragen entfernen Zeilen aus der SalesPersonQuotaHistory-Tabelle basierend auf den Verkaufszahlen des laufenden Jahres, die in der SalesPerson-Tabelle gespeichert sind.

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO


-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO


E. Verwenden eines allgemeinen Tabellenausdrucks

Im folgenden Beispiel werden nur die Zeilen gelöscht, die vom allgemeinen Tabellenausdruck zurückgegeben werden.

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen durch Angeben einer Sicht oder Tabellenvariablen gelöscht werden.

In den Beispielen in diesem Abschnitt wird das Löschen von Zeilen in einer Remotezieltabelle veranschaulicht, wobei mithilfe eines Verbindungsservers oder einer Rowsetfunktion auf die Remotetabelle verwiesen wird.

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit Tabellenhinweisen und Abfragehinweisen beim Verarbeiten der DELETE-Anweisung zeitweise das Standardverhalten des Abfrageoptimierers außer Kraft gesetzt wird.

Vorsichtshinweis Vorsicht

Da der SQL Server-Abfrageoptimierer in der Regel den optimalen Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass erfahrene Entwickler und Datenbankadministratoren Hinweise nur dann verwenden, wenn sich alle anderen Möglichkeiten als unzureichend erwiesen haben.

Im folgenden Beispiel wird der READPAST-Tabellenhinweis angegeben. Wenn READPAST angegeben ist, werden die Sperren auf Zeilen- und auf Seitenebene übersprungen. Dies führt dazu, dass das Datenbankmodul keine Zeilen und Seiten liest, die von anderen Transaktionen gesperrt sind. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO


In den Beispielen in diesem Abschnitt wird gezeigt, wie mit der OUTPUT-Klausel Informationen aus jeder von einer DELETE-Anweisung betroffenen Zeile bzw. Ausdrücke, die auf jeder Zeile basieren, zurückgegeben werden. Diese Ergebnisse können an die verarbeitende Anwendung zurückgegeben werden, die sie z. B. für Bestätigungen, Archivierungen und andere Anwendungsanforderungen verwendet.

A. Verwenden von DELETE mit der OUTPUT-Klausel

Im folgenden Beispiel wird gezeigt, wie die Ergebnisse einer DELETE-Anweisung in einer Tabellenvariablen gespeichert werden.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO


B. Verwenden von OUTPUT mit from_table_name in einer DELETE-Anweisung

Im folgenden Beispiel werden Zeilen in der ProductProductPhoto-Tabelle basierend auf Suchkriterien gelöscht, die in der FROM-Klausel der DELETE-Anweisung definiert wurden. Die OUTPUT-Klausel gibt die Spalten aus der zu löschenden Tabelle, DELETED.ProductID und DELETED.ProductPhotoID, sowie Spalten aus der Product-Tabelle zurück. Diese werden in der FROM-Klausel verwendet, um die zu löschenden Zeilen anzugeben.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO


Community-Beiträge

HINZUFÜGEN
Anzeigen: