DELETE (Transact-SQL)DELETE (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server 的表或视图中删除一行或多行。Removes one or more rows from a table or view in SQL ServerSQL Server.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
[ 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   
}  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DELETE FROM [database_name . [ schema ] . | schema. ] table_name    
    [ WHERE <search_condition> ]   
    [ OPTION ( <query_options> [ ,...n ]  ) ]  
[; ]  

参数Arguments

WITH <common_table_expression>WITH <common_table_expression>
指定在 DELETE 语句作用域内定义的临时命名结果集,也称为公用表表达式。Specifies the temporary named result set, also known as common table expression, defined within the scope of the DELETE statement. 结果集源自 SELECT 语句。The result set is derived from a SELECT statement.

公用表表达式还可与 SELECT、INSERT、UPDATE 和 CREATE VIEW 等语句一起使用。Common table expressions can also be used with the SELECT, INSERT, UPDATE, and CREATE VIEW statements. 有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP (expression) [ PERCENT ]
指定将要删除的任意行数或任意行的百分比。Specifies the number or percent of random rows that will be deleted. expression 可以是行数或行的百分比。expression can be either a number or a percent of the rows. 与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

FROMFROM
一个可选关键字,可用在 DELETE 关键字与目标 table_or_view_name 或 rowset_function_limited 之间 。An optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

table_aliastable_alias
在表示要从中删除行的表或视图的 FROM table_source 子句中指定的别名。The alias specified in the FROM table_source clause representing the table or view from which the rows are to be deleted.

server_name server_name
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

表或视图所在服务器的名称(使用链接服务器名称或 OPENDATASOURCE 函数作为服务器名称)。The name of the server (using a linked server name or the OPENDATASOURCE function as the server name) on which the table or view is located. 如果指定了 server_name,则需要 database_name 和 schema_name 。If server_name is specified, database_name and schema_name are required.

database_namedatabase_name
数据库的名称。The name of the database.

schema_nameschema_name
表或视图所属架构的名称。The name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
要从中删除行的表或视图的名称。The name of the table or view from which the rows are to be removed.

在其作用域内还可用作 DELETE 语句中的表源的表变量。A table variable, within its scope, also can be used as a table source in a DELETE statement.

table_or_view_name 引用的视图必须可更新,并且只在视图定义的 FROM 子句中引用一个基表 。The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition. 有关可更新视图的详细信息,请参阅 CREATE VIEW (Transact-SQL)For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limited rowset_function_limited
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

OPENQUERYOPENROWSET 函数,视提供程序的功能而定。Either the OPENQUERY or OPENROWSET function, subject to provider capabilities.

WITH ( <table_hint_limited> [... n] ) WITH ( <table_hint_limited> [... n] )
指定目标表允许的一个或多个表提示。Specifies one or more table hints that are allowed for a target table. 需要有 WITH 关键字和括号。The WITH keyword and the parentheses are required. 不允许 NOLOCK 和 READUNCOMMITTED。NOLOCK and READUNCOMMITTED are not allowed. 有关表提示的详细信息,请参阅表提示 (Transact-SQL)For more information about table hints, see Table Hints (Transact-SQL).

<OUTPUT_Clause><OUTPUT_Clause>
将已删除行或基于这些行的表达式作为 DELETE 操作的一部分返回。Returns deleted rows, or expressions based on them, as part of the DELETE operation. 在针对视图或远程表的任何 DML 语句中都不支持 OUTPUT 子句。The OUTPUT clause is not supported in any DML statements targeting views or remote tables. 有关详细信息,请参阅 OUTPUT 子句 (Transact-SQL)For more information, see OUTPUT Clause (Transact-SQL).

FROM table_sourceFROM table_source
指定附加的 FROM 子句。Specifies an additional FROM clause. 这个对 DELETE 的 Transact-SQLTransact-SQL 扩展允许从 <table_source> 指定数据,并从第一个 FROM 子句内的表中删除相应的行。This Transact-SQLTransact-SQL extension to DELETE allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.

这个扩展指定联接,可在 WHERE 子句中取代子查询来标识要删除的行。This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

有关详细信息,请参阅 FROM (Transact-SQL)For more information, see FROM (Transact-SQL).

WHEREWHERE
指定用于限制删除行数的条件。Specifies the conditions used to limit the number of rows that are deleted. 如果没有提供 WHERE 子句,则 DELETE 删除表中的所有行。If a WHERE clause is not supplied, DELETE removes all the rows from the table.

基于 WHERE 子句中所指定的条件,有两种形式的删除操作:There are two forms of delete operations based on what is specified in the WHERE clause:

  • 搜索删除指定搜索条件以限定要删除的行。Searched deletes specify a search condition to qualify the rows to delete. 例如,WHERE column_name = valueFor example, WHERE column_name = value.

  • 定位删除使用 CURRENT OF 子句指定游标。Positioned deletes use the CURRENT OF clause to specify a cursor. 删除操作在游标的当前位置执行。The delete operation occurs at the current position of the cursor. 这比使用 WHERE search_condition 子句限定待删除行的搜索 DELETE 语句更为精确。This can be more accurate than a searched DELETE statement that uses a WHERE search_condition clause to qualify the rows to be deleted. 如果搜索条件不唯一标识单行,则搜索 DELETE 语句删除多行。A searched DELETE statement deletes multiple rows if the search condition does not uniquely identify a single row.

<search_condition><search_condition>
指定删除行的限定条件。Specifies the restricting conditions for the rows to be deleted. 对搜索条件中可以包含的谓词数量没有限制。There is no limit to the number of predicates that can be included in a search condition. 有关详细信息,请参阅搜索条件 (Transact-SQL)For more information, see Search Condition (Transact-SQL).

CURRENT OFCURRENT OF
指定 DELETE 在指定游标的当前位置执行。Specifies that the DELETE is performed at the current position of the specified cursor.

GLOBALGLOBAL
指定 cursor_name 是指全局游标 。Specifies that cursor_name refers to a global cursor.

cursor_name cursor_name
从其中进行提取的打开游标的名称。Is the name of the open cursor from which the fetch is made. 如果同时存在名为 cursor_name 的全局游标和局部游标,那么,在指定了 GLOBAL 时,该参数是指全局游标;否则是指局部游标 。If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. 游标必须允许更新。The cursor must allow updates.

cursor_variable_name cursor_variable_name
游标变量的名称。The name of a cursor variable. 游标变量必须引用允许更新的游标。The cursor variable must reference a cursor that allows updates.

OPTION ( <query_hint> [ ,... n] ) OPTION ( <query_hint> [ ,... n] )
关键字,指示用于自定义数据库引擎Database Engine处理语句的方式的优化器提示。Keywords that indicate which optimizer hints are used to customize the way the 数据库引擎Database Engine processes the statement. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

最佳实践Best Practices

若要删除表中的所有行,请使用 TRUNCATE TABLE。To delete all the rows in a table, use TRUNCATE TABLE. TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. TRUNCATE TABLE 具有限制,例如,表不能参与复制。TRUNCATE TABLE has restrictions, for example, the table cannot participate in replication. 有关详细信息,请参阅 TRUNCATE TABLE (Transact-SQL)For more information, see TRUNCATE TABLE (Transact-SQL)

使用 @@ROWCOUNT 函数将删除的行数返回给客户端应用程序。Use the @@ROWCOUNT function to return the number of deleted rows to the client application. 有关详细信息,请参阅 @@ROWCOUNT (Transact-SQL)For more information, see @@ROWCOUNT (Transact-SQL).

错误处理Error Handling

可以通过在 TRY…CATCH 构造函数中指定 DELETE 语句,实现对该语句的错误处理。You can implement error handling for the DELETE statement by specifying the statement in a TRY...CATCH construct.

如果 DELETE 语句违反了触发器,或试图删除另一个有 FOREIGN KEY 约束的表内的数据被引用行,则可能会失败。The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. 如果 DELETE 删除了多行,而在删除的行中有任何一行违反触发器或约束,则将取消该语句,返回错误且不删除任何行。If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.

当 DELETE 语句遇到在表达式计算过程中发生的算术错误(溢出、被零除或域错误)时,数据库引擎Database Engine将处理这些错误,就好象 SET ARITHABORT 设置为 ON。When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the 数据库引擎Database Engine handles these errors as if SET ARITHABORT is set ON. 将取消批处理中的其余部分并返回错误消息。The rest of the batch is canceled, and an error message is returned.

互操作性Interoperability

如果所修改的对象是表变量,则 DELETE 可用在用户定义函数的正文中。DELETE can be used in the body of a user-defined function if the object modified is a table variable.

删除包含 FILESTREAM 列的行时,会同时删除其基础文件系统文件。When you delete a row that contains a FILESTREAM column, you also delete its underlying file system files. 基础文件是由 FILESTREAM 垃圾回收器删除的。The underlying files are removed by the FILESTREAM garbage collector. 有关详细信息,请参阅使用 Transact-SQL 访问 FILESTREAM 数据For more information, see Access FILESTREAM Data with Transact-SQL.

不能在直接或间接引用对其定义 INSTEAD OF 触发器的视图的 DELETE 语句中指定 FROM 子句。The FROM clause cannot be specified in a DELETE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger defined on it. 有关 INSTEAD OF 触发器的详细信息,请参阅 CREATE TRIGGER (Transact-SQL)For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

限制和局限Limitations and Restrictions

在将 TOP 与 DELETE 结合使用时,被引用行不按任何顺序排列,不能直接在此语句中指定 ORDER BY 子句。When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. 如果需要使用 TOP 来删除按有意义的时间顺序排列的行,您必须同时在嵌套 select 语句中使用 TOP 和 ORDER BY 子句。If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement. 请参阅本主题后面的“示例”一节。See the Examples section that follows in this topic.

对于已分区视图,不能在 DELETE 语句中使用 TOP。TOP cannot be used in a DELETE statement against partitioned views.

锁定行为Locking Behavior

默认情况下,DELETE 语句始终在其修改的表上获取排他 (X) 锁并在事务完成之前持有该锁。By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. 使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. 您可以指定表提示,以便通过指定其他锁定方法来覆盖 DELETE 语句的持续时间的这一默认行为,但只建议经验丰富的开发人员和数据库管理员将提示用作最后的手段来执行。You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

从堆删除行时, 数据库引擎Database Engine 可以使用行锁定或页锁定进行操作。When rows are deleted from a heap the 数据库引擎Database Engine may use row or page locking for the operation. 结果,删除操作导致的空页将继续分配给堆。As a result, the pages made empty by the delete operation remain allocated to the heap. 未释放空页时,数据库中的其他对象将无法重用关联的空间。When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

若要删除堆中的行并释放页,请使用下列方法之一。To delete rows in a heap and deallocate pages, use one of the following methods.

  • 在 DELETE 语句中指定 TABLOCK 提示。Specify the TABLOCK hint in the DELETE statement. 使用 TABLOCK 提示会导致删除操作获取表的排他锁,而不是行锁或页锁。Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. 这将允许释放页。This allows the pages to be deallocated. 有关 TABLOCK 提示的详细信息,请参阅表提示 (Transact-SQL)For more information about the TABLOCK hint, see Table Hints (Transact-SQL).

  • 如果要从表中删除所有行,请使用 TRUNCATE TABLE。Use TRUNCATE TABLE if all rows are to be deleted from the table.

  • 删除行之前,请为堆创建聚集索引。Create a clustered index on the heap before deleting the rows. 删除行之后,可以删除聚集索引。You can drop the clustered index after the rows are deleted. 与先前的方法相比,此方法非常耗时,并且使用更多的临时资源。This method is more time consuming than the previous methods and uses more temporary resources.

备注

通过使用 ALTER TABLE <table_name> REBUILD 语句,可随时将空白页从堆中删除。Empty pages can be removed from a heap at any time by using the ALTER TABLE <table_name> REBUILD statement.

日志记录行为Logging Behavior

始终完全记录 DELETE 语句。The DELETE statement is always fully logged.

SecuritySecurity

权限Permissions

要求对目标表具有 DELETE 权限。DELETE permissions are required on the target table. 如果语句包含 WHERE 子句,则还必须有 SELECT 权限。SELECT permissions are also required if the statement contains a WHERE clause.

默认情况下,将 DELETE 权限授予 sysadmin 固定服务器角色成员、db_ownerdb_datawriter 固定数据库角色成员以及表所有者。DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户 。Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

示例Examples

类别Category 作为特征的语法元素Featured syntax elements
基本语法Basic syntax 删除DELETE
限制删除的行数Limiting the rows deleted WHERE • FROM • 游标 •WHERE • FROM • cursor •
从远程表中删除行Deleting rows from a remote table 链接服务器 • OPENQUERY 行集函数 • OPENDATASOURCE 行集函数Linked server • OPENQUERY rowset function • OPENDATASOURCE rowset function
捕获 DELETE 语句的结果Capturing the results of the DELETE statement OUTPUT 子句OUTPUT clause

基本语法Basic Syntax

本节中的示例说明了使用最低要求的语法的 DELETE 语句的基本功能。Examples in this section demonstrate the basic functionality of the DELETE statement using the minimum required syntax.

A.A. 使用不带 WHERE 子句的 DELETEUsing DELETE with no WHERE clause

下面的示例从 SalesPersonQuotaHistory 数据库的 AdventureWorks2012AdventureWorks2012 表中删除所有行,因为该例未使用 WHERE 子句限制删除的行数。The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012AdventureWorks2012 database because a WHERE clause is not used to limit the number of rows deleted.

DELETE FROM Sales.SalesPersonQuotaHistory;  
GO  

限制删除的行数Limiting the Rows Deleted

本节中的示例演示了如何限制将被删除的行数。Examples in this section demonstrate how to limit the number of rows that will be deleted.

B.B. 使用 WHERE 子句删除行集Using the WHERE clause to delete a set of rows

下面的示例从 ProductCostHistory 数据库的 AdventureWorks2012AdventureWorks2012 表中删除 StandardCost 列的值大于 1000.00的所有行。The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012AdventureWorks2012 database in which the value in the StandardCost column is more than 1000.00.

DELETE FROM Production.ProductCostHistory  
WHERE StandardCost > 1000.00;  
GO  

下面的示例演示一个更复杂的 WHERE 子句。The following example shows a more complex WHERE clause. WHERE 子句定义要确定删除的行而必须满足的两个条件。The WHERE clause defines two conditions that must be met to determine the rows to delete. StandardCost 列中的值必须介于 12.0014.00 之间,而 SellEndDate 列中的值必须为 Null。The value in the StandardCost column must be between 12.00 and 14.00 and the value in the column SellEndDate must be null. 该示例还输出 **@@ROWCOUNT** 函数中的值,以返回已删除的行数。The example also prints the value from the **@@ROWCOUNT** function to return the number of deleted rows.

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

C.C. 使用游标以确定要删除的行Using a cursor to determine the row to delete

以下示例使用名为 my_cursor 的游标删除 AdventureWorks2012AdventureWorks2012 数据库的 EmployeePayHistory 表中的单行。The following example deletes a single row from the EmployeePayHistory table in the AdventureWorks2012AdventureWorks2012 database using a cursor named my_cursor. 删除操作只影响当前从游标提取的单行。The delete operation affects only the single row currently fetched from the cursor.

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.D. 对一个表中的数据使用联接和子查询,以删除另一个表中的行Using joins and subqueries to data in one table to delete rows in another table

下面的示例演示两种基于一个表中的数据删除另一个表中的行的方法。The following examples show two ways to delete rows in one table based on data in another table. 这两个示例均基于 SalesPerson 表中存储的年初至今的销售业绩,从 AdventureWorks2012AdventureWorks2012 数据库的 SalesPersonQuotaHistory 表中删除行。In both examples, rows from the SalesPersonQuotaHistory table in the AdventureWorks2012AdventureWorks2012 database are deleted based on the year-to-date sales stored in the SalesPerson table. 第一个 DELETE 语句显示与 ISO 兼容的子查询解决方案,第二个 DELETE 语句显示联接这两个表的 Transact-SQLTransact-SQL FROM 扩展。The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQLTransact-SQL FROM extension to join the two tables.

-- SQL-2003 Standard subquery  
  
DELETE FROM Sales.SalesPersonQuotaHistory   
WHERE BusinessEntityID IN   
    (SELECT BusinessEntityID   
     FROM Sales.SalesPerson   
     WHERE SalesYTD > 2500000.00);  
GO  
-- Transact-SQL extension  
  
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  
-- No need to mention target table more than once.  
  
DELETE spqh  
  FROM  
        Sales.SalesPersonQuotaHistory AS spqh  
    INNER JOIN Sales.SalesPerson AS sp  
        ON spqh.BusinessEntityID = sp.BusinessEntityID  
  WHERE  sp.SalesYTD > 2500000.00;  

E.E. 使用 TOP 限制删除的行数Using TOP to limit the number of rows deleted

当 TOP (n) 子句与 DELETE 一起使用时,将针对随机选择的 n 行执行删除操作 。When a TOP (n) clause is used with DELETE, the delete operation is performed on a random selection of n number of rows. 以下示例从 AdventureWorks2012AdventureWorks2012 数据库的 PurchaseOrderDetail 表中删除到期日期早于 2006 年 7 月 1 日的 20 个随机行。The following example deletes 20 random rows from the PurchaseOrderDetail table in the AdventureWorks2012AdventureWorks2012 database that have due dates that are earlier than July 1, 2006.

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

如果需要使用 TOP 来删除按有意义的时间顺序排列的行,您必须同时使用 TOP 和 ORDER BY 子句。If you have to use TOP to delete rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement. 下面的查询从 PurchaseOrderDetail 表中删除了其到期日期最早的 10 行。The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. 为了确保仅删除 10 行,嵌套 Select 语句 (PurchaseOrderID) 中指定的列将成为表的主键。To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID) is the primary key of the table. 如果指定列包含重复的值,则在嵌套 Select 语句中使用非键列可能会导致删除的行超过 10 个。Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.

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

从远程表中删除行Deleting Rows From a Remote Table

本节中的示例说明如何使用链接服务器行集函数引用一个远程表,以便从该表中删除行。Examples in this section demonstrate how to delete rows from a remote table by using a linked server or a rowset function to reference the remote table. 远程表存在于不同的服务器或 SQL Server 实例上。A remote table exists on a different server or instance of SQL Server.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

F.F. 通过使用链接服务器从远程表删除数据Deleting data from a remote table by using a linked server

下面的示例将删除远程表中的行。The following example deletes rows from a remote table. 该示例从使用 sp_addlinkedserver 创建指向远程数据源的链接开始。The example begins by creating a link to the remote data source by using sp_addlinkedserver. 然后,将链接服务器名称 MyLinkServer 指定为 server.catalog.schema.object 形式的由四个部分组成的对象名称的一部分 。The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form 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  

G.G. 通过使用 OPENQUERY 函数从远程表删除数据Deleting data from a remote table by using the OPENQUERY function

下面的示例通过指定 OPENQUERY 行集函数从远程表删除行。The following example deletes rows from a remote table by specifying the OPENQUERY rowset function. 在之前例子中创建的链接服务器名称用于此示例。The linked server name created in the previous example is used in this example.

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

H.H. 通过使用 OPENDATASOURCE 函数从远程表删除数据Deleting data from a remote table by using the OPENDATASOURCE function

以下示例通过指定 OPENDATASOURCE 行集函数从远程表中删除行。The following example deletes rows from a remote table by specifying the OPENDATASOURCE rowset function. 通过使用 server_name 或 server_name\instance_name 格式,为该数据源指定一个有效的服务器名称 。Specify a valid server name for the data source by using the format server_name or server_name\instance_name.

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

捕获 DELETE 语句的结果Capturing the results of the DELETE statement

I.I. 使用带有 OUTPUT 子句的 DELETEUsing DELETE with the OUTPUT clause

以下示例演示如何将 DELETE 语句的结果保存到 AdventureWorks2012AdventureWorks2012 数据库的表变量中。The following example shows how to save the results of a DELETE statement to a table variable in the AdventureWorks2012AdventureWorks2012 database.

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  

J.J. 在 DELETE 语句中同时使用 OUTPUT 与 <from_table_name>Using OUTPUT with <from_table_name> in a DELETE statement

以下示例根据 DELETE 语句的 FROM 子句中定义的搜索条件,删除 AdventureWorks2012AdventureWorks2012 数据库的 ProductProductPhoto 表中的行。The following example deletes rows in the ProductProductPhoto table in the AdventureWorks2012AdventureWorks2012 database based on search criteria defined in the FROM clause of the DELETE statement. OUTPUT 子句返回所删除表中的列( DELETED.ProductIDDELETED.ProductPhotoID)以及 Product 表中的列。The OUTPUT clause returns columns from the table being deleted, DELETED.ProductID, DELETED.ProductPhotoID, and columns from the Product table. FROM 子句中使用该项来指定要删除的行。This is used in the FROM clause to specify the rows to delete.

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  

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

K.K. 从表中删除所有行Delete all rows from a table

下面的示例从 Table1 表中删除所有行,因为该例未使用 WHERE 子句限制删除的行数。The following example deletes all rows from the Table1 table because a WHERE clause is not used to limit the number of rows deleted.

DELETE FROM Table1;  

L.L. 从表中删除一组行DELETE a set of rows from a table

以下示例从 Table1 表中删除 StandardCost 列的值大于 1000.00 的所有行。The following example deletes all rows from the Table1 table that have a value greater than 1000.00 in the StandardCost column.

DELETE FROM Table1  
WHERE StandardCost > 1000.00;  

M.M. 通过 DELETE 语句使用 LABELUsing LABEL with a DELETE statement

以下示例将标签与 DELETE 语句一起使用。The following example uses a label with the DELETE statement.

DELETE FROM Table1  
OPTION ( LABEL = N'label1' );  
  

N.N. 通过 DELETE 语句使用标签和查询提示Using a label and a query hint with the DELETE statement

此查询显示通过 DELETE 语句使用查询联接提示的基本语法。This query shows the basic syntax for using a query join hint with the DELETE statement. 有关联接提示以及如何使用 OPTION 子句的详细信息,请参阅 OPTION 子句 (Transact-SQL)For more information on join hints and how to use the OPTION clause, see OPTION Clause (Transact-SQL).

-- Uses AdventureWorks  
  
DELETE FROM dbo.FactInternetSales  
WHERE ProductKey IN (   
    SELECT T1.ProductKey FROM dbo.DimProduct T1   
    JOIN dbo.DimProductSubcategory T2  
    ON T1.ProductSubcategoryKey = T2.ProductSubcategoryKey  
    WHERE T2.EnglishProductSubcategoryName = 'Road Bikes' )  
OPTION ( LABEL = N'CustomJoin', HASH JOIN ) ;  

另请参阅See Also

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