DELETE (Transact-SQL)

更新日期： 2006 年 4 月 14 日

从表或视图中删除行。

Transact-SQL 语法约定

语法

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

参数

• WITH <common_table_expression>
  指定在 DELETE 语句作用域内定义的临时命名结果集，也称为公用表表达式。结果集源自 SELECT 语句。

  公用表表达式还可与 SELECT、INSERT、UPDATE 和 CREATE VIEW 等语句一起使用。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

• TOP (expression) [ PERCENT ]
  指定将要删除的任意行数或任意行的百分比。expression 可以为行数或行的百分比。与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。

  在 INSERT、UPDATE 和 DELETE 语句中，需要使用括号分隔 TOP 中的 expression。有关详细信息，请参阅 TOP (Transact-SQL)。

• FROM
  可选的关键字，可用在 DELETE 关键字与目标 table_or_view_name 或 rowset_function_limited 之间。

• server_name
  表或视图所在服务器的名称（使用链接服务器名称或 OPENDATASOURCE 函数作为服务器名称）。如果指定了 server_name，则需要 database_name 和 schema_name。

• database_name
  数据库的名称。

• schema_name
  该表或视图所属架构的名称。

• table
  要从其中删除行的表的名称。

  在其作用域内还可用作 DELETE 语句中的表源的 table 变量。

• WITH ( <table_hint_limited> [... n] )
  指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和括号。不允许 NOLOCK 和 READUNCOMMITTED。有关表提示的详细信息，请参阅表提示 (Transact-SQL)。

• view_name
  要从其中删除行的视图的名称。

  view_name 引用的视图必须可更新，并且在该视图的 FROM 子句中只引用一个基表。有关可更新视图的详细信息，请参阅 CREATE VIEW (Transact-SQL)。

• rowset_function_limited
  OPENQUERY 或 OPENROWSET 函数，视提供程序的功能而定。有关访问接口所需功能的详细信息，请参阅 OLE DB 访问接口的 UPDATE 和 DELETE 语句要求。

• table_valued_function
  可以是表值函数。有关表值函数的详细信息，请参阅表值用户定义函数。

• <OUTPUT_Clause>
  将已删除行或基于这些行的表达式作为 DELETE 操作的一部分返回。在针对视图或远程表的任何 DML 语句中都不支持 OUTPUT 子句。有关详细信息，请参阅OUTPUT 子句 (Transact-SQL)。

• FROM <table_source>
  指定附加的 FROM 子句。这个对 DELETE 的 Transact-SQL 扩展允许从 <table_source> 指定数据，并从第一个 FROM 子句内的表中删除相应的行。

  这个扩展指定联接，可在 WHERE 子句中取代子查询来标识要删除的行。

  有关详细信息，请参阅 FROM (Transact-SQL)。

• WHERE
  指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。

  基于 WHERE 子句中所指定的条件，有两种形式的删除操作：

  • 搜索删除指定搜索条件以限定要删除的行。例如，WHERE column_name = value。
  • 定位删除使用 CURRENT OF 子句指定游标。删除操作在游标的当前位置执行。这比使用 WHERE search_condition 子句限定要删除的行的搜索 DELETE 语句更为精确。如果搜索条件不唯一标识单行，则搜索 DELETE 语句删除多行。

• <search_condition>
  指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。有关详细信息，请参阅搜索条件 (Transact-SQL)。

• CURRENT OF
  指定 DELETE 在指定游标的当前位置执行。

• GLOBAL
  指定 cursor_name 是指全局游标。

• cursor_name
  从其中进行提取的打开游标的名称。如果同时存在名为 cursor_name 的全局游标和局部游标，那么，在指定 GLOBAL 时，此参数是指全局游标；否则是指局部游标。游标必须允许更新。

• cursor_variable_name
  游标变量的名称。游标变量必须引用允许更新的游标。

• OPTION ( <query_hint> [ ,... n] )
  关键字，指示优化器提示用于自定义数据库引擎处理语句的方式。有关详细信息，请参阅查询提示 (Transact-SQL)。

备注

如果所修改的对象是 table 变量，则 DELETE 可用在用户定义函数的正文中。

如果 DELETE 语句违反了触发器，或试图删除另一个有 FOREIGN KEY 约束的表内的数据被引用行，则可能会失败。如果 DELETE 删除了多行，而在删除的行中有任何一行违反触发器或约束，则将取消该语句，返回错误且不删除任何行。

当 DELETE 语句遇到在表达式计算过程中发生的算术错误（溢出、被零除或域错误）时，数据库引擎将处理这些错误，就好象 SET ARITHABORT 设置为 ON。将取消批处理中的其余部分并返回错误消息。

对远程表和本地及远程分区视图上的 DELETE 语句将忽略 SET ROWCOUNT 选项的设置。

如果要删除表中的所有行，请使用未指定 WHERE 子句的 DELETE 语句，或者使用 TRUNCATE TABLE。TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

从堆中删除行

从堆删除行时，数据库引擎 可以使用行锁定或页锁定进行操作。结果，删除操作导致的空页将继续分配给堆。未释放空页时，数据库中的其他对象将无法重用关联的空间。

若要删除堆中的行并释放页，请使用下列方法之一。

• 在 DELETE 语句中指定 TABLOCK 提示。使用 TABLOCK 提示会导致删除操作获取表的共享锁，而不是行锁或页锁。这将允许释放页。有关 TABLOCK 提示的详细信息，请参阅表提示 (Transact-SQL)。
• 如果要从表中删除所有行，请使用 TRUNCATE TABLE。
• 删除行之前，请为堆创建聚集索引。删除行之后，可以删除聚集索引。与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。

有关锁定的详细信息，请参阅数据库引擎中的锁定。

对 DELETE 操作使用 INSTEAD OF 触发器

如果在对表或视图的 DELETE 操作上定义了 INSTEAD OF 触发器，则执行该触发器而不执行 DELETE 语句。SQL Server 的早期版本只支持 DELETE 和其他数据修改语句上的 AFTER 触发器。不能在直接或间接引用对其定义 INSTEAD OF 触发器的视图的 DELETE 语句中指定 FROM 子句。有关 INSTEAD OF 触发器的详细信息，请参阅 CREATE TRIGGER (Transact-SQL)。

权限

要求对目标表具有 DELETE 权限。如果语句包含 WHERE 子句，则还必须有 SELECT 权限。

默认情况下，将 DELETE 权限授予 sysadmin 固定服务器角色成员、db_owner 和 db_datawriter 固定数据库角色成员以及表所有者。sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户。

示例

A. 使用不带 WHERE 子句的 DELETE

下面的示例从 SalesPersonQuotaHistory 表中删除所有行，因为该例未使用 WHERE 子句限制删除的行数。

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. 对行集使用 DELETE

下面的示例从 ProductCostHistory 表中删除 StandardCost 列的值大于 1000.00 的所有行。

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

C. 对游标的当前行使用 DELETE

以下示例使用名为 complex_cursor 的游标删除 EmployeePayHistory 表中的单行。删除操作只影响当前从游标提取的单行。

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. 基于子查询使用 DELETE 或使用 Transact-SQL 扩展

下面的示例显示用于从基于联接或相关子查询的基表中删除记录的 Transact-SQL 扩展插件。第一条 DELETE 语句显示与 SQL 2003 兼容的子查询解决方案，第二条 DELETE 语句显示该 Transact-SQL 扩展插件。两个查询都将从 SalesPersonQuotaHistory 表中删除行，该表基于 SalesPerson 表中所存储的本年度迄今为止的销售业绩。

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

使用带有 TOP 子句的 DELETE

以下示例删除 ProductInventory 表中所有行（27 行）的 2.5%。

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

F. 使用带有 OUTPUT 子句的 DELETE

下面的示例将删除 Sales.ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* INTO @MyTableVar 指定 DELETE 语句的结果（即，已删除的行中的所有列）返回到 @MyTableVartable 变量。其后的两个 SELECT 语句返回 @MyTableVar 中的值以及 ShoppingCartItem 表中删除操作的结果。

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

下面是 SELECT 语句的结果集：

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. 在 DELETE 语句中同时使用 OUTPUT 与 from_table_name

以下示例根据 DELETE 语句的 FROM 子句中定义的搜索条件，删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表中的列（DELETED.ProductID、DELETED.ProductPhotoID）以及 Product 表中的列。在 FROM 子句中使用该项来指定要删除的行。

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

请参阅

参考

CREATE TRIGGER (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)
WITH common_table_expression (Transact-SQL)

其他资源

删除表中的数据

帮助和信息

获取 SQL Server 2005 帮助

更改历史记录

发布日期	历史记录
2006 年 4 月 14 日	新增内容： 在“备注”部分中，添加了“从堆中删除行”部分。