DELETE (Transact-SQL)

Entfernt Zeilen aus einer Tabelle oder Sicht.

Transact-SQL-Syntaxkonventionen

Syntax

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <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_or view_name
  Der Name der Tabelle oder Sicht, aus der die Zeilen entfernt werden sollen.

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

  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 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 Tabellenhinweise (Transact-SQL).

• <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 einen Join 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 Database Engine (Datenbankmodul) anzupassen. Weitere Informationen finden Sie unter Abfragehinweise (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 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.

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.

Das Verwenden von SET ROWCOUNT wird in einer künftigen Version von SQL Server keine Auswirkungen auf die Anweisungen DELETE, INSERT und UPDATE haben. Verwenden Sie SET ROWCOUNT bei neuen Entwicklungsarbeiten nicht zusammen mit den Anweisungen DELETE, INSERT und UPDATE, und planen Sie die Änderung von Anwendungen, die SET ROWCOUNT derzeit verwenden. Stattdessen wird die Verwendung der TOP-Klausel empfohlen.

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

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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
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 einem Join 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 AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE BusinessEntityID IN 
    (SELECT BusinessEntityID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
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 AdventureWorks2008R2;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

F. Verwenden von DELETE mit der OUTPUT-Klausel

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

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

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

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 AdventureWorks2008R2;
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

Verweis

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Konzepte

Erste Schritte mit FILESTREAM-Speicherung

Andere Ressourcen

Löschen von Daten aus einer Tabelle