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

  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 ステートメントで 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 AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

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

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

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

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

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

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 互換のサブクエリ ソリューションを示し、2 つ目の 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. DELETE を TOP 句と共に使用する

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

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

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

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

次の例では、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 ストレージの概要

その他の技術情報

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