DELETE (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric のウェアハウス

SQL Server のテーブルまたはビューから 1 つ以上の行を削除します。

Transact-SQL 構文表記規則

構文

-- 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 and Microsoft Fabric

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

Note

SQL Server 2014 (12.x) 以前のバージョンの 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_name または rowset_function_limited の間で使用できる、省略可能なキーワードです。

table_alias
FROM table_source 句で指定される別名です。行を削除するテーブルまたはビューを表します。

server_name
適用対象: SQL Server 2008 (10.0.x) 以降。

テーブルまたはビューがあるサーバー名 (リンクされたサーバー名またはサーバー名として OPENDATASOURCE 関数を使用) です。 server_name が指定されている場合、database_name と schema_name が必要です。

database_name
データベースの名前。

schema_name
テーブルまたはビューが属するスキーマの名前です。

table_or_view_name
行を削除するテーブルまたはビューの名前です。

テーブル変数は、そのスコープ内では、DELETE ステートメントでテーブル ソースとしても使用できます。

table_or_view_name が参照するビューは更新可能であることが条件となります。また、そのビュー定義の FROM 句ではベース テーブルを 1 つだけ参照している必要があります。 更新可能なビューの詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。

rowset_function_limited
適用対象: SQL Server 2008 (10.0.x) 以降。

プロバイダーの機能によって、OPENQUERY 関数、または OPENROWSET 関数のどちらかです。

WITH (<table_hint_limited> [... n] )
対象のテーブルに設定可能なテーブル ヒントを 1 つ以上指定します。 キーワード WITH とかっこが必要です。 NOLOCK および READUNCOMMITTED は指定できません。 テーブル ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

<OUTPUT_Clause>
DELETE 操作の一部として、削除された行または行に基づく式を返します。 OUTPUT 句は、ビューまたはリモート テーブルを対象とする DML ステートメントではサポートされません。 この句の引数と動作の詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

FROM table_source
追加の FROM 句を指定します。 DELETE に追加されたこの Transact-SQL 拡張機能を使用すると、<table_source> のデータを指定して、最初の FROM 句のテーブルから対応する行を削除できます。

WHERE 句内のサブクエリを使用する代わりに、この拡張機能で結合を指定して、削除する行を特定できます。

詳細については、「FROM (Transact-SQL)」を参照してください。

WHERE
削除する行数を制限するときに使用する条件を指定します。 WHERE 句を指定しない場合は、DELETE によってテーブルからすべての行が削除されます。

WHERE 句に指定する内容によって、削除操作は次の 2 種類に分けられます。

  • 検索結果削除。削除する行を限定する検索条件を指定します。 たとえば、WHERE column_name = value のように使います。

  • 位置指定削除。CURRENT OF 句を使用してカーソルを指定します。 削除操作は、カーソルの現在の位置で発生します。 位置指定削除は、WHERE search_condition 句によって削除する行を限定する、検索結果削除の DELETE ステートメントよりも正確です。 検索結果削除の DELETE ステートメントでは、検索条件で 1 つの行が一意に識別されない場合、複数の行が削除されます。

<search_condition>
削除する行を制限する条件を指定します。 検索条件に含まれる述語の数に制限はありません。 詳細については、「検索条件 (Transact-SQL)」を参照してください。

CURRENT OF
指定したカーソルの現在位置で DELETE を実行します。

GLOBAL
cursor_name でグローバル カーソルを参照することを指定します。

cursor_name
フェッチが行われるオープン カーソルの名前を指定します。 cursor_name という名前のグローバル カーソルとローカル カーソルの両方がある場合、GLOBAL を指定すると、この引数はグローバル カーソルを参照します。GLOBAL を指定しないと、この引数はローカル カーソルを参照します。 カーソルは、更新可能である必要があります。

cursor_variable_name
カーソル変数の名前を指定します。 カーソル変数は、更新可能なカーソルを参照する必要があります。

OPTION (<query_hint> [ ,... n] )
データベース エンジンのステートメント処理をカスタマイズするためのオプティマイザー ヒントを示すキーワードです。 詳細については、「 クエリ ヒント (Transact-SQL)」を参照してください。

ベスト プラクティス

テーブル内のすべての行を削除するには、TRUNCATE TABLE を使用します。 DELETE よりも TRUNCATE TABLE の方が高速で、システムとトランザクション ログのリソース使用量も少なくて済みます。 TRUNCATE TABLE には制限があり、たとえば、テーブルをレプリケーションに参加させることはできません。 詳細については、「TRUNCATE TABLE (Transact-SQL)」を参照してください

@@ROWCOUNT 関数を使用して、削除される行数をクライアント アプリケーションに返します。 詳細については、「@@ROWCOUNT (Transact-SQL)」を参照してください。

エラー処理

TRY...CATCH 構造でステートメントを指定すると、DELETE ステートメントのエラー処理を実装できます。

DELETE ステートメントは、トリガーに違反したり、FOREIGN KEY 制約で別のテーブル内のデータによって参照されている行を削除しようとしたりすると、失敗するおそれがあります。 DELETE で複数の行を削除するときに、削除される行のいずれかがトリガーまたは制約に違反すると、ステートメントは取り消され、エラーが返されます。行は削除されません。

DELETE ステートメントで式の評価中に算術エラー (オーバーフロー、0 による除算、またはドメイン エラー) が発生すると、データベース エンジン により SET ARITHABORT が ON に設定されている場合と同様にこれらのエラーが処理されます。 残りのバッチは取り消され、エラー メッセージが返されます。

相互運用性

変更するオブジェクトがテーブル変数の場合は、ユーザー定義関数内で DELETE を使用できます。

FILESTREAM 列を含む行を削除すると、その基となるファイル システム ファイルも削除されます。 基になるファイルは、FILESTREAM ガベージ コレクターによって削除されます。 詳しくは、「Transact-SQL による FILESTREAM データへのアクセス」をご覧ください。

FROM 句は、INSTEAD OF トリガーが定義されているビューを直接または間接的に参照する DELETE ステートメントでは指定できません。 INSTEAD OF トリガーの詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。

現在、Microsoft Fabric のウェアハウスでは、DELETE ステートメントに FROM 句を指定できません。

制限事項と制約事項

TOP を DELETE と共に使用する場合、参照される行は任意の順序に並べられません。また、このステートメントで、ORDER BY 句を直接指定することはできません。 TOP を使用して、意味のある時系列順に行を削除する必要がある場合は、サブセレクト ステートメントで TOP と ORDER BY を一緒に使用する必要があります。 例については、後の「例」のセクションを参照してください。

パーティション ビューに対して DELETE ステートメントで TOP を使用することはできません。

ロック動作

既定では、DELETE ステートメントは、変更するテーブル オブジェクトとページに対するインテント排他 (IX) ロックと、変更する行に対する排他 (X) ロックを常に取得し、トランザクションが完了するまでそれらのロックを保持します。

インテント排他 (IX) ロックをかけたトランザクション以外はその同じデータ セットを変更できませんが、NOLOCK ヒントまたは READ UNCOMMITTED 分離レベルが使用されている場合に限り、読み取り操作は行うことができます。 別のロック手法を指定することで DELETE ステートメントの期間のこの既定の動作をオーバーライドするテーブル ヒントを指定できます。ただし、このヒントは、経験豊富な開発者およびデータベース管理者が最後の手段としてのみ使用することを推奨します。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

ヒープから行を削除するときには、 データベース エンジン によって、操作に行またはページ ロックが使用されることがあります。 その結果、削除操作で空になったページがヒープに割り当てられたままになります。 空のページの割り当てが解除されないと、データベース内の他のオブジェクトで該当の領域を再利用できなくなります。

ヒープ内の行を削除し、ページの割り当てを解除するには、次のいずれかの方法を使用します。

  • DELETE ステートメントで TABLOCK ヒントを指定します。 TABLOCK ヒントを使用すると、削除操作では、行またはページ ロックではなく、オブジェクトの IX ロックが取得されます。 これにより、ページの割り当てを解除できるようになります。 TABLOCK ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

  • テーブルからすべての行を削除する場合は、TRUNCATE TABLE を使用します。

  • 行を削除する前に、ヒープにクラスター化インデックスを作成します。 作成したクラスター化インデックスは、行を削除した後、削除できます。 この方法は前の 2 つの方法より時間がかかり、一時リソースがより多く使用されます。

Note

空のページは、いつでも ALTER TABLE <table_name> REBUILD ステートメントを使ってヒープから削除できます。

ログ記録の動作

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 を使用する

次の例では、削除する行数を制限する WHERE 句が指定されていないため、AdventureWorks2022 データベース内の SalesPersonQuotaHistory テーブルからすべての行が削除されます。

DELETE FROM Sales.SalesPersonQuotaHistory;  
GO

削除する行数を制限する

このセクションの例では、削除する行数を制限する方法を示します。

B. WHERE 句を使用して行セットを削除する

次の例では、StandardCost 列の値が 1000.00 より大きいすべての行を AdventureWorks2022 データベース内の ProductCostHistory テーブルから削除します。

DELETE FROM Production.ProductCostHistory  
WHERE StandardCost > 1000.00;  
GO

次の例では、より複雑な WHERE 句を示します。 WHERE 句では、削除する行を決定するために満たす必要がある 2 つの条件を定義しています。 StandardCost 列の値が 12.00 から 14.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 というカーソルを使用している 1 行を AdventureWorks2022 データベースの EmployeePayHistory テーブルから削除します。 この操作では、カーソルから現在フェッチされている 1 行だけが削除されます。

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. 1 つのテーブルへの結合およびサブクエリを使用して、別のテーブルの行を削除する

次の例では、1 つのテーブル内の行を、別のテーブルのデータに基づいて削除する 2 つの方法を示します。 どちらの例も、SalesPerson テーブルに格納されている今年に入ってからの売り上げに基づいて、AdventureWorks2022 データベースの SalesPersonQuotaHistory テーブルから行を削除します。 最初の DELETE ステートメントは、ISO 互換のサブクエリ ソリューションを示しています。また、2 つ目の DELETE ステートメントは、2 つのテーブルを結合する 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 を使用して削除する行数を制限する

DELETE ステートメントで TOP (n) 句を使用した場合、ランダムに選択される 'n' 行に対して削除操作が実行されます。 次の例では、納期が 2006 年 7 月 1 日より早い 20 行を AdventureWorks2022 データベースの PurchaseOrderDetail テーブルからランダムに選択して削除します。

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

TOP を使用して、意味のある順序で行を削除する必要がある場合は、サブセレクト ステートメントで ORDER BY を指定して TOP を使用する必要があります。 次のクエリでは、納期が早いものから 10 行を PurchaseOrderDetail テーブルから削除します。 10 行だけを確実に削除するために、サブセレクト ステートメントではテーブルの主キーの列 (PurchaseOrderID) を指定しています。 サブセレクト ステートメントで非キー列を指定すると、指定した列に重複する値が含まれる場合、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 (10.0.x) 以降。

F. リンク サーバーを使用してリモート テーブルからデータを削除する

次の例では、リモート テーブルの行を削除します。 sp_addlinkedserver を使用してリモート データ ソースへのリンクを作成した後、 server.catalog.schema.object という形式の、4 つの要素で構成されたオブジェクト名の一部として、リンク サーバー名 MyLinkServer を指定します。

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'AdventureWorks2022';  
GO  

-- Specify the remote data source using a four-part name   
-- in the form linked_server.catalog.schema.object.  
  
DELETE MyLinkServer.AdventureWorks2022.HumanResources.Department 
WHERE DepartmentID > 16;  
GO

G. OPENQUERY 関数を使用してリモート テーブルからデータを削除する

次の例では、OPENQUERY 行セット関数を指定してリモート テーブルから行を削除します。 この例では、前の例で作成したリンク サーバー名を使用します。

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

H. OPENDATASOURCE 関数を使用してリモート テーブルからデータを削除する

次の例では、OPENDATASOURCE 行セット関数を指定してリモート テーブルから行を削除します。 server_name または server_name\instance_name という形式を使用して、データ ソースの有効なサーバー名を指定します。

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

DELETE ステートメントの結果をキャプチャする

I. DELETE を OUTPUT 句と共に使用する

次の例では、DELETE ステートメントの結果を AdventureWorks2022 データベースのテーブル変数に保存する方法を示します。

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. OUTPUT を DELETE ステートメント内で <from_table_name> と共に使用する

次の例では、DELETE ステートメントの FROM 句で定義された検索条件に基づいて、AdventureWorks2022 データベースの ProductProductPhoto テーブルの行を削除します。 OUTPUT 句では、削除されるテーブルの列 ( DELETED.ProductID、 DELETED.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、Analytics Platform System (PDW)

K. テーブルからすべての行を削除する

次の例では、削除する行数を制限する WHERE 句が指定されていないため、Table1 テーブルからすべての行が削除されます。

DELETE FROM Table1;

L. テーブルから行のセットを削除する

次の例では、StandardCost 列の値が 1000.00 より大きいすべての行を Table1 テーブルから削除します。

DELETE FROM Table1  
WHERE StandardCost > 1000.00;

M. DELETE ステートメントで LABEL を使用する

次の例では、DELETE ステートメントでラベルを使用します。

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

北 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 句を使用した削除

このクエリでは、FROM 句ではなく、WHERE 句を使用して削除する方法が示されています。

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 dbo.Table2.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)