DELETE (Transact-SQL)

移除資料表或檢視中的資料列。

Transact-SQL 語法慣例

語法

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <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 運算式所參考的資料列並不依照任何順序來排列。

  TOP 中用來分隔 expression 的括號，在 INSERT、UPDATE 和 DELETE 陳述式中是必要的。如需詳細資訊，請參閱＜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_or view_name
  這是要移除資料列的資料表或檢視名稱。

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

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

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

備註

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

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

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

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

使用 SET ROWCOUNT 並不會影響 SQL Server 未來版本的 DELETE、INSERT 和 UPDATE 陳述式。請勿在新的開發工作中使用 SET ROWCOUNT 搭配 DELETE、INSERT 和 UPDATE 陳述式，並請規劃修改目前正在使用它的應用程式。我們建議您改用 TOP 子句。

如果您要刪除資料表中的所有資料列，請使用未指定 WHERE 子句的 DELETE 陳述式，或使用 TRUNCATE TABLE。TRUNCATE TABLE 的速度比 DELETE 快，使用的系統資源和交易記錄資源也比較少。

刪除堆積中的資料列

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

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

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

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

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

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

在 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 AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. 在一組資料列上使用 DELETE

下列範例會刪除 ProductCostHistory 資料表中，所有 StandardCost 資料行值超出 1000.00 的資料列。

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

C. 在資料指標目前的資料列上使用 DELETE

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

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

D. 使用以子查詢為基礎的 DELETE 以及使用 Transact-SQL 延伸模組

下列範例會顯示從以聯結或相關子查詢為基礎的基底資料表中刪除記錄時，所用的 Transact-SQL 延伸模組。第一個 DELETE 陳述式會顯示 ISO 相容的子查詢方案，而第二個 DELETE 陳述式會顯示 Transact-SQL 延伸模組。這兩個查詢都會從以 SalesPerson 資料表所儲存之年度目前銷售情況為基礎的 SalesPersonQuotaHistory 資料表中移除資料列。

-- SQL-2003 Standard subquery

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

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

E. 搭配 TOP 子句使用 DELETE

下列範例會刪除 ProductInventory 資料表中 2.5% 的資料列 (27 列)。

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

F. 搭配 OUTPUT 子句使用 DELETE

下列範例示範如何將 DELETE 陳述式的結果儲存到資料表變數中。

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

G. 在 DELETE 陳述式中，搭配 from_table_name 來使用 OUTPUT

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

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

概念

FILESTREAM 儲存體使用者入門

其他資源

刪除資料表的資料