DELETE (Transact-SQL)

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

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

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

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Verwenden von DELETE für eine Zeilenmenge

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

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

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

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

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

F. Verwenden von DELETE mit der OUTPUT-Klausel

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

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

Konzepte

Andere Ressourcen