DELETE (Transact-SQL)

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

從SQL Server中的資料表或檢視中移除一或多個資料列。

主題連結圖示Transact-SQL 語法慣例

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

[ WITH <common_table_expression> [ ,...n ] ] 
DELETE [database_name . [ schema ] . | schema. ] table_name  
FROM [database_name . [ schema ] . | schema. ] table_name 
JOIN {<join_table_source>}[ ,...n ]  
ON <join_condition>
[ WHERE <search_condition> ]   
[ OPTION ( <query_options> [ ,...n ]  ) ]  
[; ]  

<join_table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias 
    [ <tablesample_clause>]  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
}  
-- Syntax for Parallel Data Warehouse  
  
DELETE 
    [ FROM [database_name . [ schema ] . | schema. ] table_name ]   
    [ WHERE <search_condition> ]   
    [ OPTION ( <query_options> [ ,...n ]  ) ]  
[; ]  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

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 運算式所參考的資料列並不依照任何順序來排列。 如需詳細資訊,請參閱 TOP (Transact-SQL)

FROM
這是一個選擇性的關鍵字,可用於 DELETE 關鍵字和目標 table_or_view_namerowset_function_limited 之間。

table_alias
在 FROM table_source 子句中指定的別名,代表要刪除資料列的資料表或檢視。

server_name
適用于:SQL Server 2008 和更新版本。

資料表或檢視所在的伺服器名稱 (使用連結的伺服器名稱或 OPENDATASOURCE 函式當作伺服器名稱)。 若指定 server_name,則 database_nameschema_name 都為必要項目。

database_name
資料庫的名稱。

schema_name
資料表或檢視所屬之結構描述的名稱。

table_or_view_name
要移除資料列的資料表或檢視名稱。

資料表變數,在自身範圍內也可用來做為 DELETE 陳述式中的資料表來源。

table_or_view_name 所參考的檢視必須能夠更新,而且必須只參考該檢視定義之 FROM 子句中的單一基底資料表。 如需可更新檢視的詳細資訊,請參閱 CREATE VIEW (Transact-SQL)

rowset_function_limited
適用于:SQL Server 2008 和更新版本。

依提供者功能而定,會是 OPENQUERYOPENROWSET 函式。

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] )
關鍵字,它們會指出要使用哪一個最佳化工具提示來自訂 Database Engine 處理陳述式的方式。 如需詳細資訊,請參閱 查詢提示 (Transact-SQL)

最佳做法

若要刪除資料表中的所有資料列,請使用 TRUNCATE TABLETRUNCATE TABLE 比 DELETE 快,而且使用較少的系統和交易記錄資源。 TRUNCATE TABLE 有限制,例如,資料表無法參與複寫。 如需詳細資訊,請參閱 TRUNCATE TABLE (Transact-SQL)

使用 函 @@ROWCOUNT 式,將已刪除的資料列數目傳回用戶端應用程式。 如需詳細資訊,請參閱 @@ROWCOUNT (Transact-SQL)

錯誤處理

您可以在 建構中 TRY...CATCH 指定 語句,以實作 DELETE 子句的錯誤處理。

如果語句違反觸發程式,或嘗試移除另一個資料表 FOREIGN KEY 中具有條件約束的資料所參考的資料列,語句 DELETE 可能會失敗。 DELETE如果 移除多個資料列,且其中任何一個移除的資料列違反觸發程式或條件約束,則會取消語句、傳回錯誤,而且不會移除任何資料列。

當 DELETE 子句遇到算術錯誤 (溢位、除以零或運算式評估期間發生的網域錯誤) 時,Database Engine 會處理這些錯誤,就像設定 ON 一樣 SET ARITHABORT 。 此時會取消批次的其餘部分,且會傳回錯誤訊息。

互通性

如果修改的物件是資料表變數,就可以在使用者自訂函數的主體中使用 DELETE。

當您刪除包含 FILESTREAM 資料行的資料列時,也會刪除其基礎檔案系統的檔案。 基礎檔案會由 FILESTREAM 記憶體回收行程所移除。 如需詳細資訊,請參閱使用 Transact-SQL 存取 FILESTREAM 資料

FROM 子句不能在 DELETE 子句中指定,該語句會直接或間接參考其上定義觸發程式的檢視 INSTEAD OF 。 如需 INSTEAD OF 觸發程式的詳細資訊,請參閱 CREATE TRIGGER (Transact-SQL)

限制事項

搭配 使用 DELETETOP ,參考的資料列不會依任何順序排列,而且 ORDER BY 無法直接在此語句中指定 子句。 如果您需要使用 TOP 以有意義的時間順序刪除資料列,則必須 TOP 搭配子選取語句中的 子句一起使用 ORDER BY 。 請參閱本主題稍後的<範例>一節。

TOP 無法在語句中 DELETE 針對資料分割檢視使用。

鎖定行為

根據預設, DELETE 語句一律會在資料表物件上取得意圖獨佔 (IX) 鎖定,以及它修改的頁面、它修改的資料列的獨佔 (X) 鎖定,以及保留這些鎖定,直到交易完成為止。

使用意圖獨佔 (IX) 鎖定,其他交易就無法修改相同的資料集;讀取作業只能搭配使用 NOLOCK 提示或讀取未認可的隔離等級進行。 您可以指定資料表提示,透過指定其他鎖定方法來覆寫 DELETE 陳述式持續時間的這個預設行為,但是,我們建議僅將提示做為由資深開發人員及資料庫系統管理員採取的最後手段。 如需詳細資訊,請參閱 Transact-SQL) (資料表提示

從堆積中刪除資料列時,Database Engine 可能會針對作業使用資料列或頁面鎖定。 如此一來,由刪除作業清空的頁面仍然會配置給堆積。 如果未取消空白頁面的配置,資料庫中的其他物件就無法重複使用相關聯的空間。

若要刪除堆積中的資料列及取消配置頁面,請使用下列其中一個方法。

  • 在 DELETE 陳述式中指定 TABLOCK 提示。 使用 TABLOCK 提示會導致刪除作業在 物件上取得 IX 鎖定,而不是資料列或頁面鎖定。 如此可允許取消配置頁面。 如需 TABLOCK 提示的詳細資訊,請參閱 Transact-SQL) (資料表提示

  • 如果要從資料表刪除所有資料列,請使用 TRUNCATE TABLE

  • 請先在堆積上建立叢集索引之後,再刪除資料列。 您可以在刪除資料列之後卸除叢集索引。 這個方法會比之前的方法耗用更多的時間,而且會使用更多的暫存資源。

注意

您可以隨時使用 ALTER TABLE <table_name> REBUILD 陳述式將空白頁面從堆積移除。

記錄行為

DELETE 陳述式一律會完整記錄。

安全性

權限

需要目標資料表的 DELETE 權限。 如果陳述式包含 WHERE 子句,則也需要 SELECT 權限。

DELETE 權限預設為 sysadmin 固定伺服器角色、db_ownerdb_datawriter 固定資料庫角色的成員,以及資料表擁有者。 sysadmindb_ownerdb_securityadmin 角色的成員及資料表擁有者可將權限移轉給其他使用者。

範例

類別 代表性語法元素
基本語法 刪除
限制刪除的資料列 WHERE • FROM • 資料指標 •
從遠端資料表刪除資料列 連結的伺服器 • OPENQUERY 資料列集函數 • OPENDATASOURCE 資料列集函數
擷取 DELETE 陳述式的結果 OUTPUT 子句

基本語法

本節的範例會使用最少的所需語法來示範 DELETE 陳述式的基本功能。

A. 使用不含 WHERE 子句的 DELETE

下列範例會刪除 SalesPersonQuotaHistory AdventureWorks2019 資料庫中資料表中的所有資料列,因為 WHERE 子句不會用來限制已刪除的資料列數目。

DELETE FROM Sales.SalesPersonQuotaHistory;  
GO  

限制刪除的資料列

本節中的範例會顯示如何限制將會遭到刪除的資料列數目。

B. 使用 WHERE 子句刪除一組資料列

下列範例會刪除 ProductCostHistory AdventureWorks2019 資料庫中資料表中的所有資料列,其中資料行中的 StandardCost 值大於 1000.00

DELETE FROM Production.ProductCostHistory  
WHERE StandardCost > 1000.00;  
GO  

下列範例會顯示更加複雜的 WHERE 子句。 WHERE 子句會定義為了判斷要刪除之資料列而必須符合的兩個條件。 StandardCost 資料行中的值必須介於 12.0014.00 之間,而且 SellEndDate 資料行中的值必須為 Null。 此範例也會列印 @@ROWCOUNT 函式中的值,以傳回已刪除的資料列數目。

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. 使用資料指標來判斷要刪除的資料列

下列範例會使用名為 complex_cursor 的資料指標,從 EmployeePayHistory AdventureWorks2019 資料庫中的資料表中刪除單一資料列。 刪除作業只會影響目前從資料指標中提取的單一資料列。

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. 針對某個資料表中的資料使用聯結和子查詢,以刪除其他資料表中的資料列

下列範例會顯示兩種方法,可根據其他資料表中的資料來刪除某個資料表中的資料列。 在這兩個範例中,AdventureWorks2019 資料庫中資料表的資料列 SalesPersonQuotaHistory 都會根據資料表中 SalesPerson 儲存的年度銷售量來刪除。 第一個 DELETE 語句會顯示 ISO 相容的子查詢解決方案,第二個 DELETE 語句會顯示 Transact-SQL FROM 延伸模組以聯結這兩個數據表。

-- 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. 使用 TOP 限制刪除的資料列數目

當 TOP (n) 子句與 DELETE 一起使用時,會隨機選取 n 個資料列來執行刪除作業。 下列範例會 20PurchaseOrderDetail AdventureWorks2019 資料庫中具有早于 2006 年 7 月 1 日之到期日之資料表中刪除亂數據列。

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

如果您必須使用 TOP 依有意義的時序來刪除資料列,就必須在 subselect 陳述式中同時使用 TOP 和 ORDER BY。 下列查詢會刪除 PurchaseOrderDetail 資料表中具有最早到期日的 10 個資料列。 為確保只刪除 10 個資料列,subselect 陳述式 (PurchaseOrderID) 中指定的資料行是資料表的主索引鍵。 如果指定的資料行包含重複值,則在 subselect 陳述式中使用非索引鍵資料行會造成刪除 10 個以上的資料列。

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

從遠端資料表刪除資料列

本節的範例會顯示如何使用 連結的伺服器資料列集函式來參考遠端資料表,以便刪除遠端資料表的資料列。 遠端資料表存在於 SQL Server 的不同伺服器或執行個體上。

適用于:SQL Server 2008 和更新版本。

F. 使用連結的伺服器刪除遠端資料表的資料

下列範例會刪除遠端資料表的資料列。 此範例一開始會使用 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  

G. 使用 OPENQUERY 函數刪除遠端資料表的資料

下列範例會藉由指定 OPENQUERY 資料列集函式來刪除遠端資料表的資料列。 上一個範例所建立之連結的伺服器名稱會用於這個範例。

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

H. 使用 OPENDATASOURCE 函數刪除遠端資料表的資料

下列範例會藉由指定 OPENDATASOURCE 資料列集函式來刪除遠端資料表的資料列。 使用 server_nameserver_name\instance_name 格式,為資料來源指定有效的伺服器名稱。

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

擷取 DELETE 陳述式的結果

I. 搭配 OUTPUT 子句使用 DELETE

下列範例示範如何將語句的結果 DELETE 儲存至 AdventureWorks2019 資料庫中的資料表變數。

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. 在 DELETE 子句中使用 OUTPUT 搭配 < from_table_name >

下列範例會根據 語句 子句 DELETE 中所定義的搜尋準則,刪除 ProductProductPhoto AdventureWorks2019 資料庫中資料表中的資料 FROM 列。 OUTPUT 子句會傳回所刪除的資料表的 DELETED.ProductIDDELETED.ProductPhotoID資料行及 Product 資料表中的資料行。 FROM 子句藉此來指定要刪除的資料列。

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 Synapse Analytics and Analytics Platform System (PDW)

K. 刪除資料表中的所有資料列

下列範例會刪除 Table1 資料表中的所有資料列,因為並未利用 WHERE 子句來限制刪除的資料列數。

DELETE FROM Table1;  

L. 刪除資料表中的一組資料列

下列範例會刪除 Table1 資料行中 StandardCost 值大於 1000.00 的所有資料表資料列。

DELETE FROM Table1  
WHERE StandardCost > 1000.00;  

M. 搭配 DELETE 陳述式使用 LABEL

下列範例會搭配 DELETE 陳述式使用標籤。

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

N. 搭配 DELETE 陳述式使用標籤及查詢提示

此查詢示會示範查詢聯結提示與 DELETE 陳述式搭配使用的基本語法。 如需有關聯結提示及如何使用 OPTION 子句的詳細資訊,請參閱 OPTION 子句 (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 ) ;  

O. 使用 WHERE 子句刪除

此查詢會顯示如何使用 WHERE 子句刪除,而不是 使用 FROM 子句。

DELETE tableA WHERE EXISTS (
SELECT TOP 1 1 FROM tableB tb WHERE tb.col1 = tableA.col1
)

P. 根據聯結另一個資料表的結果進行刪除

這個範例示範如何根據與另一個資料表聯結的結果來從資料表中刪除。

CREATE TABLE dbo.Table1   
    (ColA int NOT NULL, ColB decimal(10,3) NOT NULL);  
GO  

CREATE TABLE dbo.Table2   
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);  
GO  
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0);  
INSERT INTO dbo.Table2 VALUES(1, 0.0);  
GO  

DELETE dbo.Table2   
FROM dbo.Table2   
    INNER JOIN dbo.Table1   
    ON (dbo.Table2.ColA = dbo.Table1.ColA)
    WHERE dboTable2.ColA = 1;  

另請參閱

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)