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

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

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

• WITH ( <table_hint_limited> [...n] )
  対象のテーブルに設定可能なテーブル ヒントを指定します。キーワード 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)」を参照してください。

説明

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

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

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

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

SQL Server の将来のリリースでは、SET ROWCOUNT を使用しても、DELETE、INSERT、および UPDATE ステートメントが影響を受けることはありません。新しい開発作業では DELETE、INSERT、および UPDATE ステートメントでの SET ROWCOUNT の使用を避け、現在 SET ROWCOUNT を使用しているアプリケーションは変更を検討してください。代わりに TOP 句を使用することをお勧めします。

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

ヒープからの行の削除

DELETE 操作での INSTEAD OF トリガーの使用

権限

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

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

使用例

A. WHERE 句を指定せずに DELETE を使用する

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. 行セットに対して DELETE を使用する

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

C. カーソルの現在行で DELETE を使用する

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 拡張機能を使用する

-- 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. DELETE を TOP 句と共に使用する

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

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

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

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

関連項目

参照

概念

その他の技術情報