DELETE (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 ]
    [ FROMtable_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 陳述式衍生而來。如需詳細資訊，請參閱＜WITH common_table_expression (Transact-SQL)＞。

• TOP ( expression) [ PERCENT ]
  指定要刪除的隨機資料列數或百分比。expression 可以是一個數字，也可以是資料列的百分比。搭配 DELETE 使用的 TOP 運算式所參考的資料列並不依照任何順序來排列。

  TOP 中用來分隔 expression 的括號，在 INSERT、UPDATE、MERGE 和 DELETE 陳述式中是必要的。如需詳細資訊，請參閱＜TOP (Transact-SQL)＞。

• FROM
  一個選擇性的關鍵字，您可以在 DELETE 關鍵字和目標物件之間使用它。

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

• server_name
  這是資料表或檢視表所在之連結伺服器的名稱。server_name 可以指定為連結的伺服器名稱，或使用 OPENDATASOURCE 函數來指定。

  當 server_name 指定為連結的伺服器時，database_name 和 schema_name 就是必要參數。當 server_name 是使用 OPENDATASOURCE 來指定時，database_name 和 schema_name 可能無法套用至所有資料來源，而且受限於存取遠端物件之 OLE DB 提供者的功能。如需詳細資訊，請參閱＜分散式查詢＞。

• database_name
  資料庫的名稱。

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

• table_or view_name
  要從中移除資料列的資料表或檢視表名稱。

  table_or_view_name 所參考的檢視表必須能夠更新，且必須只參考檢視表之 FROM 子句中的一個基底資料表。如需有關可更新檢視表的詳細資訊，請參閱＜CREATE VIEW (Transact-SQL)＞。

• rowset_function_limited
  依提供者功能而定，這是 OPENQUERY 或 OPENROWSET 函數。如需有關提供者所需功能的詳細資訊，請參閱＜OLE DB 提供者的 UPDATE 與 DELETE 需求＞。

• WITH (table_hint_limited [...n] )
  指定目標資料表允許使用的一個或多個資料表提示。WITH 關鍵字和括號都是必要的。不允許使用 NOLOCK 和 READUNCOMMITTED。如需有關資料表提示的詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

• @table\_variable
  指定資料表變數。

• <OUTPUT_Clause>
  在刪除作業中，傳回已刪除的資料列或以它們為基礎的運算式。在以本機資料分割檢視表、分散式資料分割檢視表、遠端資料表或遠端檢視表為目標的任何 DML 陳述式中，都不支援 OUTPUT 子句。如需詳細資訊，請參閱＜OUTPUT 子句 (Transact-SQL)＞。

• FROM table_source
  指定可用來聯結目標 table_or view_name 與 <table_source> 的其他 FROM 子句，以識別要移除的資料列。您可以利用 DELETE 的 Transact-SQL 延伸模組來取代 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 TABLE。TRUNCATE TABLE 的速度比 DELETE 快，使用的系統資源和交易記錄資源也比較少。

您可以使用 @@ROWCOUNT 函數，將刪除的資料列數目傳回給用戶端應用程式。如需詳細資訊，請參閱＜@@ROWCOUNT (Transact-SQL)＞。

相容性支援

使用 SET ROWCOUNT 不會影響 SQL Server 下一版本中的 DELETE 陳述式。請勿在新的開發工作中使用 SET ROWCOUNT 搭配 DELETE 陳述式，並請將目前使用它的應用程式修改成使用 TOP 語法。

錯誤處理

您可以在 TRY…CATCH 建構中指定 DELETE 陳述式，實作此陳述式的錯誤處理。如需詳細資訊，請參閱＜在 Transact-SQL 中使用 TRY...CATCH＞。

如果 DELETE 陳述式違反觸發程序，或試圖移除含有 FOREIGN KEY 條件約束的另一個資料表中之資料所參考的資料列，DELETE 陳述式便可能失敗。如果 DELETE 移除了多個資料列，且有任何移除的資料列違反了觸發程序或條件約束，就會取消陳述式，傳回錯誤，且不會移除任何資料列。

當 DELETE 陳述式遇到在運算式評估期間發生算術錯誤 (溢位、除以零或範圍錯誤) 時，Database Engine 會依照 SET ARITHABORT 設為 ON 的方式來處理這些錯誤。此時會取消批次的其餘部分，且會傳回錯誤訊息。

互通性

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

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

在直接或間接參考定義了 INSTEAD OF 觸發程序之檢視表的 DELETE 陳述式中，不能指定 FROM 子句。如需有關 INSTEAD OF 觸發程序的詳細資訊，請參閱＜CREATE TRIGGER (Transact-SQL)＞。

限制事項

搭配 DELETE 使用 TOP 時，並不會依任何順序排列參考的資料列，而且不能直接在這個陳述式中指定 ORDER BY 子句。如果您必須使用 TOP 依有意義的時序來刪除資料列，就必須在 subselect 陳述式中搭配 ORDER BY 子句一起使用 TOP。請參閱本主題稍後的「範例」一節。

針對資料分割檢視表進行的 DELETE 陳述式中不得使用 TOP。

針對遠端資料表及本機和遠端資料分割檢視表來進行的 DELETE 陳述式，其 SET ROWCOUNT 選項的設定會被忽略。

鎖定行為

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

• 在 DELETE 陳述式中指定 TABLOCK 提示。使用 TABLOCK 提示會造成刪除作業對資料表進行共用鎖定，而非資料列或頁面鎖定。如此可允許取消配置頁面。如需有關 TABLOCK 提示的詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

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

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

如需有關鎖定的詳細資訊，請參閱＜Database Engine 中的鎖定＞。

記錄行為

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

權限

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

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

範例

基本語法

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

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

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

指定要刪除的資料列

本節的範例會示範用來限制要刪除之資料列數的方法。

A. 使用 WHERE 子句來限制資料列

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

B. 使用 TOP 子句

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

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

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

C. 使用資料指標來刪除目前的資料列

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. 使用子查詢以及使用 Transact-SQL FROM 延伸模組

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

E. 使用通用資料表運算式

指定標準資料表以外的目標物件

本節的範例示範如何指定檢視表或資料表變數來刪除資料列。

刪除遠端資料表中的資料列

本節的範例示範如何刪除遠端目標資料表中的資料列，其方式是使用連結的伺服器或資料列集函數參考遠端資料表。

使用提示來覆寫查詢最佳化工具的預設行為

本節的範例示範如何使用資料表提示和查詢提示，在處理 DELETE 陳述式時暫時覆寫查詢最佳化工具的預設行為。

警告
由於 SQL Server 查詢最佳化工具通常會選取最好的查詢執行計畫，因此，我們建議資深的開發人員和資料庫管理員將提示當做最後的解決辦法。

下列範例指定資料表提示 READPAST。當指定 READPAST 時，將會略過資料列層級和頁面層級的鎖定，這樣會造成 Database Engine 不讀取其他交易鎖定的資料列和頁面。如需詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

擷取 DELETE 陳述式的結果

本節的範例示範如何使用 OUTPUT 子句傳回 DELETE 陳述式所影響之每個資料列的資訊，或是以該資料列為根據的運算式。這些結果可以傳回給負責處理的應用程式，以便用在確認訊息、封存或其他這類應用程式需求等用途上。

A. 搭配 OUTPUT 子句使用 DELETE

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match 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

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

請參閱

參考

概念