DELETE (Transact-SQL)

更新 : 2006 年 4 月 14 日

テーブルまたはビューから行を削除します。

Transact-SQL 構文表記規則

構文

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <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
  行を削除するテーブルの名前を指定します。

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

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

• view_name
  行を削除するビューの名前を指定します。

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

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

• table_valued_function
  テーブル値関数を指定します。テーブル値関数の詳細については、「ユーザー定義テーブル値関数」を参照してください。

• <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 ステートメントでは無視されます。

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

ヒープからの行の削除

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

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

• DELETE ステートメントで TABLOCK ヒントを指定します。TABLOCK ヒントを使用すると、削除操作では、行またはページ ロックではなく、テーブルの共有ロックが取得されます。これにより、ページの割り当てを解除できるようになります。TABLOCK ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
• テーブルからすべての行を削除する場合は、TRUNCATE TABLE を使用します。
• 行を削除する前に、ヒープにクラスタ化インデックスを作成します。作成したクラスタ化インデックスは、行を削除した後、削除できます。この方法は前の 2 つの方法より時間がかかり、一時リソースがより多く使用されます。

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

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

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

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

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

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

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

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

次の例では、complex_cursor というカーソルを使用している 1 行を EmployeePayHistory テーブルから削除します。この操作では、カーソルから現在フェッチされている 1 行だけが削除されます。

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. サブクエリに基づく DELETE と、Transact-SQL 拡張機能を使用する

次の例では、Transact-SQL の拡張機能を使用し、結合または相関サブクエリに基づいてベース テーブルからレコードを削除します。最初の DELETE ステートメントは SQL-2003 と互換性のあるサブクエリ ソリューションを示し、2 つ目の DELETE ステートメントは Transact-SQL 拡張機能を示しています。どちらのクエリも、SalesPerson テーブルに格納されている年度累計の売り上げに基づいて、SalesPersonQuotaHistory テーブルから行を削除します。

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

次の例では、ProductInventory テーブルの行の 2.5 パーセント (27 行) を削除します。

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

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

次の例では、Sales.ShoppingCartItem テーブルのすべての行を削除します。OUTPUT DELETED.* INTO @MyTableVar 句では、DELETE ステートメントの結果 (削除された行のすべての列) を @MyTableVartable 変数に返すように指定します。この後に、@MyTableVar 内の値、および ShoppingCartItem テーブルの削除操作の結果を返す 2 つの SELECT ステートメントが続きます。

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

SELECT ステートメントの結果を次に示します。

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

G. DELETE ステートメントで OUTPUT と from_table_name を使用する

次の例では、DELETE ステートメントの FROM 句で定義された検索条件に基づいて、ProductProductPhoto テーブルの行を削除します。OUTPUT 句では、削除されるテーブルの列 (DELETED.ProductID、DELETED.ProductPhotoID)、および Product テーブルの列を返します。これは FROM 句で削除する行を指定するときに使用されます。

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

参照

関連項目

CREATE TRIGGER (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)
WITH common_table_expression (Transact-SQL)

その他の技術情報

テーブル内のデータの削除

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴