DELETE (Transact-SQL)

Artikel
12/15/2008

Aktualisiert: 14. April 2006

Entfernt Zeilen aus einer Tabelle oder Sicht.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <OUTPUT Clause> ]
    [ FROM <table_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 
}

Argumente

WITH <common_table_expression>
Gibt das temporäre benannte Resultset, auch als allgemeiner Tabellenausdruck bezeichnet, an, das im Rahmen der DELETE-Anweisung definiert wurde. Das Resultset wird aus der SELECT-Anweisung abgeleitet.

Allgemeine Tabellenausdrücke können außerdem mit SELECT-, INSERT-, UPDATE- und CREATE VIEW-Anweisungen verwendet werden. 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 INSERT, UPDATE oder DELETE verwendeten TOP-Ausdruck verwiesen wird, sind nicht in einer bestimmten Reihenfolge angeordnet.

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

FROM
Ein optionales Schlüsselwort, das zwischen dem DELETE-Schlüsselwort und dem Ziel-table_or_view_name oder -rowset_function_limited verwendet werden kann.

server_name
Der Name des Servers (mithilfe eines Verbindungsservernamens oder der OPENDATASOURCE-Funktion als Servername), auf dem sich die Tabelle oder die Sicht befindet. Wenn server_name angegeben ist, sind database_name und schema_name erforderlich.

database_name
Der Name der Datenbank.

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

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

In ihrem Gültigkeitsbereich kann eine table-Variable auch als Quelltabelle in einer DELETE-Anweisung verwendet werden.

WITH ( <table_hint_limited> [... n] )
Gibt einen oder mehrere Tabellenhinweise an, die 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 Tabellenhinweis (Transact-SQL).

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

Die Sicht, auf die 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.

table_valued_function
Kann eine Tabellenwertfunktion darstellen. Weitere Informationen zu Tabellenwertfunktionen finden Sie unter Benutzerdefinierte Tabellenwertfunktionen.

<OUTPUT_Clause>
Gibt gelöschte Zeilen bzw. auf diesen basierende Ausdrücke als Teil der DELETE-Operation zurück. Die OUTPUT-Klausel wird in DML-Anweisungen, deren Ziel Sichten oder Remotetabellen sind, nicht unterstützt. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

FROM <table_source>
Gibt eine zusätzliche FROM-Klausel an. Diese Transact-SQL-Erweiterung für DELETE ermöglicht es Ihnen, Daten aus <table_source> anzugeben und die entsprechenden Zeilen aus der Tabelle in der ersten FROM-Klausel zu löschen.

Diese Erweiterung, die eine Verknüpfung angibt, kann anstelle einer Unterabfrage in der WHERE-Klausel verwendet werden, um zu entfernende Zeilen zu identifizieren.

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

WHERE
Gibt die Bedingungen an, die zur Beschränkung der Anzahl der gelöschten 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 sind, 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 Datenbankmodul anzupassen. Weitere Informationen finden Sie unter Abfragehinweis (Transact-SQL).

Hinweise

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

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 Datenbankmodul den Fehler so, als wäre SET ARITHABORT auf ON festgelegt. Der Rest des Batches wird abgebrochen, und eine Fehlermeldung wird zurückgegeben.

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.

Sollen alle Zeilen in einer Tabelle gelöscht werden, verwenden Sie die DELETE-Anweisung ohne eine WHERE-Klausel, oder verwenden Sie TRUNCATE TABLE. TRUNCATE TABLE ist schneller als DELETE und verwendet weniger Systemressourcen und Ressourcen für die Transaktionsprotokollierung.

Löschen von Zeilen aus einem Heap

Wenn Zeilen aus einem Heap gelöscht werden, können von 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 gemeinsame 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 Tabellenhinweis (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.

Verwenden eines INSTEAD OF-Triggers für DELETE-Aktionen

Wenn ein INSTEAD OF-Trigger für DELETE-Aktionen für eine Tabelle oder Sicht definiert ist, wird der Trigger anstelle der DELETE-Anweisung ausgeführt. Frühere Versionen von SQL Server unterstützen nur AFTER-Trigger für DELETE- und andere Anweisungen zur Datenänderung. 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).

Berechtigungen

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

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

Beispiele

A. Verwenden von DELETE ohne WHERE-Klausel

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

B. Verwenden von DELETE für eine Zeilenmenge

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

C. Verwenden von DELETE für die aktuelle Zeile eines Cursors

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 von DELETE basierend auf einer Unterabfrage und Verwenden der Transact-SQL-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 SQL-2003-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 von DELETE mit der TOP-Klausel

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

F. Verwenden von DELETE mit der OUTPUT-Klausel

Im folgenden Beispiel werden alle Zeilen der Sales.ShoppingCartItem-Tabelle gelöscht. Die OUTPUT DELETED.* INTO @MyTableVar-Klausel gibt an, dass die Ergebnisse der DELETE-Anweisung, d. h. alle Spalten in den gelöschten Zeilen, an die @MyTableVartable-Variable zurückgegeben werden. Dann folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar sowie die Ergebnisse des Löschvorgangs in der ShoppingCartItem-Tabelle zurückgeben.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

Im Folgenden werden die Resultsets der SELECT-Anweisungen angezeigt:

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

G. 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

Siehe auch

Änderungsverlauf

Version	Verlauf
14. April 2006	Neuer Inhalt: Im Abschnitt mit den Hinweisen wurde der Abschnitt zum Löschen von Zeilen aus einem Heap hinzugefügt.

Freigeben über