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 式で参照される行は、任意の順序で並べられません。

  INSERT、UPDATE、MERGE、および DELETE の各ステートメントで TOP を使用する場合は、expression をかっこで区切る必要があります。詳細については、「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 句ではベース テーブルを 1 つだけ参照している必要があります。更新可能なビューの詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。

• rowset_function_limited
  プロバイダーの機能に応じて、OPENQUERY 関数、または OPENROWSET 関数を指定します。プロバイダーで必要な機能の詳細については、「UPDATE と DELETE を使用するための OLE DB プロバイダの要件」を参照してください。

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

• @table\_variable
  テーブル変数を指定します。

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

• FROM table_source
  追加の FROM 句を指定します。この FROM 句を使用して対象になる table_or view_name を <table_source> と結合し、削除する行を指定できます。WHERE 句内のサブクエリを使用する代わりに、DELETE のこの Transact-SQL 拡張機能を使用できます。

  詳細については、「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 が指定された場合、この引数はグローバル カーソルを参照します。それ以外の場合は、ローカル カーソルを参照します。カーソルは、更新可能である必要があります。

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

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

ベスト プラクティス

テーブルからすべての行を削除するには、TRUNCATE TABLE を使用します。DELETE と比べると TRUNCATE TABLE の方が高速で、システムとトランザクション ログのリソース使用量も少なくて済みます。

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

互換性サポート

SQL Server の次のリリースでは、SET ROWCOUNT を使用しても DELETE ステートメントには影響しなくなります。新しい開発作業では DELETE ステートメントで SET ROWCOUNT を使用しないようにし、現在 SET ROWCOUNT を使用しているアプリケーションは TOP 構文を使用するように変更してください。

エラー処理

TRY...CATCH 構造でステートメントを指定することで、DELETE ステートメントのエラー処理を実装できます。詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。

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

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

相互運用性

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

FILESTREAM 列を含む行を削除すると、その基になるファイル システム ファイルも削除されます。基になるファイルは、FILESTREAM ガベージ コレクターによって削除されます。詳細については、「Transact-SQL を使用した FILESTREAM データの管理」を参照してください。

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

制限事項と制約事項

DELETE と共に TOP を使用する場合、参照された行の並べ替えは行われません。このステートメントで ORDER BY 句を直接指定することはできません。TOP を使用して意味のある日時順に行を削除する必要がある場合は、サブセレクト ステートメントに ORDER BY 句を含めて TOP を使用する必要があります。このトピックの「例」を参照してください。

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

SET ROWCOUNT オプションの設定は、リモート テーブルと、ローカルおよびリモート パーティション ビューに対する DELETE ステートメントでは無視されます。

ロック動作

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

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

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

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

ロックの詳細については、「データベース エンジンのロック」を参照してください。

ログ記録の動作

DELETE ステートメントは、常にすべてログに記録されます。

権限

対象テーブルに対する DELETE 権限が必要です。ステートメントに WHERE 句が含まれる場合は、SELECT 権限も必要です。

DELETE 権限は、既定では sysadmin 固定サーバー ロール、db_owner および db_datawriter 固定データベース ロールのメンバーとテーブル所有者に与えられています。sysadmin、db_owner、および db_securityadmin の各ロールのメンバーとテーブル所有者は、他のユーザーに権限を譲渡できます。

例

基本構文

このセクションの例では、最低限必要な構文を使用して DELETE ステートメントの基本機能を示します。

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

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 を指定すると行レベルのロックとページ レベルのロックの両方がスキップされ、その結果データベース エンジンは、他のトランザクションによってロックされている行やページを読み取りません。詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

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

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

このセクションの例では、OUTPUT 句を使用して、DELETE ステートメントの影響を受ける各行の情報やこれらの行に基づく式を返す方法を示します。これらの結果は、確認メッセージ、アーカイブ化、その他のアプリケーション要件で使用するために、処理アプリケーションに返すことができます。

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

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

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

関連項目

参照

概念