DELETE (Transact-SQL)

从 SQL Server 2012 中的表或视图中移除一行或多行。

Transact-SQL 语法约定

语法

[ 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> ]
    [ 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。

• TOP (expression) [ PERCENT ]
  指定将要删除的任意行数或任意行的百分比。 expression 可以是行数或行的百分比。 与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。 有关详细信息，请参阅 TOP (Transact-SQL)。

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

• table_alias
  在表示要从中删除行的表或视图的 FROM table_source 子句中指定的别名。

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

• database_name
  数据库的名称。

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

• table_or view_name
  要从中删除行的表或视图的名称。

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

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

• rowset_function_limited
  OPENQUERY 或 OPENROWSET 函数，视提供程序的功能而定。

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

• <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)。

最佳实践

若要删除表中的所有行，请使用 TRUNCATE TABLE。 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

使用 @@ROWCOUNT 函数将删除的行数返回给客户端应用程序。 有关详细信息，请参阅 @@ROWCOUNT (Transact-SQL)。

错误处理

您可以通过在 TRY…CATCH 构造函数中指定 DELETE 语句，实现对该语句的错误处理。

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

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

互操作性

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

删除包含 FILESTREAM 列的行时，会同时删除其基础文件系统文件。 基础文件是由 FILESTREAM 垃圾回收器删除的。 有关详细信息，请参阅使用 Transact-SQL 访问 FILESTREAM 数据。

不能在直接或间接引用对其定义 INSTEAD OF 触发器的视图的 DELETE 语句中指定 FROM 子句。 有关 INSTEAD OF 触发器的详细信息，请参阅 CREATE TRIGGER (Transact-SQL)。

限制和局限

在将 TOP 与 DELETE 结合使用时，被引用行不按任何顺序排列，不能直接在此语句中指定 ORDER BY 子句。 如果需要使用 TOP 来删除按有意义的时间顺序排列的行，您必须同时在嵌套 select 语句中使用 TOP 和 ORDER BY 子句。 请参阅本主题后面的“示例”一节。

对于已分区视图，不能在 DELETE 语句中使用 TOP。

锁定行为

默认情况下，DELETE 语句始终在其修改的表上获取排他 (X) 锁并在事务完成之前持有该锁。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。 您可以指定表提示，以便通过指定其他锁定方法来覆盖 DELETE 语句的持续时间的这一默认行为，但只建议经验丰富的开发人员和数据库管理员将提示用作最后的手段来执行。 有关详细信息，请参阅表提示 (Transact-SQL)。

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

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

• 在 DELETE 语句中指定 TABLOCK 提示。 使用 TABLOCK 提示会导致删除操作获取表的排他锁，而不是行锁或页锁。 这将允许释放页。 有关 TABLOCK 提示的详细信息，请参阅表提示 (Transact-SQL)。

• 如果要从表中删除所有行，请使用 TRUNCATE TABLE。

• 删除行之前，请为堆创建聚集索引。 删除行之后，可以删除聚集索引。 与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。

日志记录行为

始终完全记录 DELETE 语句。

安全性

权限

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

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

示例

类别	作为特征的语法元素
基本语法	DELETE
限制删除的行数	WHERE • FROM • 游标 •
从远程表中删除行	链接服务器 • OPENQUERY 行集函数 • OPENDATASOURCE 行集函数
通过使用提示覆盖查询优化器的默认行为	表提示 • 查询提示
捕获 DELETE 语句的结果	OUTPUT 子句

基本语法

本节中的示例说明了使用最低要求的语法的 DELETE 语句的基本功能。

A.使用不带 WHERE 子句的 DELETE

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

USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

限制删除的行数

本节中的示例演示了如何限制将被删除的行数。

A.使用 WHERE 子句删除行集

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

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

下面的示例演示一个更复杂的 WHERE 子句。 WHERE 子句定义要确定删除的行而必须满足的两个条件。 StandardCost 列中的值必须介于 12.00 与 14.00 之间，而 SellEndDate 列中的值必须为 Null。 该示例还将打印 @@ROWCOUNT 函数中的值，以返回已删除的行数。

USE AdventureWorks2012;
GO
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00
      AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));

B.使用游标以确定要删除的行

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

USE AdventureWorks2012;
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

C.对一个表中的数据使用联接和子查询，以删除另一个表中的行

下面的示例演示两种基于一个表中的数据删除另一个表中的行的方法。 在这两个示例中，都将从 SalesPersonQuotaHistory 表中删除行，该表基于 SalesPerson 表中存储的本年度迄今为止的销售业绩。 第一条 DELETE 语句显示与 ISO 兼容的子查询解决方案，第二条 DELETE 语句显示 Transact-SQL FROM 扩展插件以联接这两个表。

-- SQL-2003 Standard subquery

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

-- Transact-SQL extension
USE AdventureWorks2012;
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

A.使用 TOP 限制删除的行数

当 TOP (n) 子句与 DELETE 一起使用时，将针对随机选择的第 n 行执行删除操作。 下面的示例从 PurchaseOrderDetail 表中删除了其到期日期早于 2006 年 7 月 1 日的 20 个随机行。

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

如果需要使用 TOP 来删除按有意义的时间顺序排列的行，您必须同时使用 TOP 和 ORDER BY 子句。 下面的查询从 PurchaseOrderDetail 表中删除了其到期日期最早的 10 行。 为了确保仅删除 10 行，嵌套 Select 语句 (PurchaseOrderID) 中指定的列将成为表的主键。 如果指定列包含重复的值，则在嵌套 Select 语句中使用非键列可能会导致删除的行超过 10 个。

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

从远程表中删除行

本节中的示例说明如何通过使用链接服务器或行集函数引用一个远程表，以便从该表中删除行。 远程表存在于不同的服务器或 SQL Server 实例上。

A.通过使用链接服务器从远程表删除数据

下面的示例将删除远程表中的行。 该示例从使用 sp_addlinkedserver 创建指向远程数据源的链接开始。 然后，链接服务器名称 MyLinkServer 指定为 server.catalog.schema.object 形式的由四个部分组成的对象名称的一部分。

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2012';
GO

-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16;
GO

B.通过使用 OPENQUERY 函数从远程表删除数据

下面的示例通过指定 OPENQUERY 行集函数从远程表删除行。 在之前例子中创建的链接服务器名称用于此示例。

DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO

C.通过使用 OPENDATASOURCE 函数从远程表删除数据

下面的示例通过指定 OPENDATASOURCE 行集函数从远程表删除行。 通过使用 server_name 或 server_name\instance_name 格式，为该数据源指定一个有效的服务器名称。

DELETE FROM OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Department 
WHERE DepartmentID = 17;'

捕获 DELETE 语句的结果

A.使用带有 OUTPUT 子句的 DELETE

以下示例演示如何将 DELETE 语句的结果保存到一个表变量中。

USE AdventureWorks2012;
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

B.在 DELETE 语句中对 <from_table_name> 使用 OUTPUT

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

USE AdventureWorks2012;
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)

@@ROWCOUNT (Transact-SQL)