DELETE (Transact-SQL)
SQL Server 2012 のテーブルまたはビューから 1 つ以上の行を削除します。
.gif "トピック リンク アイコン")
構文
[ 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
}
引数
WITH <common_table_expression>
DELETE ステートメントのスコープ内で定義された、一時的な名前付き結果セット (共通テーブル式とも呼ばれる) を指定します。 結果セットは SELECT ステートメントから派生します。共通テーブル式は、SELECT、INSERT、UPDATE、CREATE VIEW の各ステートメントでも使用できます。 詳細については、「WITH Common」を参照してください。
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
テーブルまたはビューがあるサーバー名 (リンクされたサーバー名またはサーバー名として 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
プロバイダーの機能によって、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 の方が高速で、システムとトランザクション ログのリソース使用量も少なくて済みます。
@@ROWCOUNT 関数を使用して、削除される行数をクライアント アプリケーションに返します。 詳細については、「@@ROWCOUNT (Transact-SQL)」を参照してください。
エラー処理
TRY...CATCH 構造でステートメントを指定することで、DELETE ステートメントのエラー処理を実装できます。
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)」を参照してください。
制限事項と制約事項
TOP を DELETE と共に使用する場合、参照される行は任意の順序に並べられません。また、このステートメントで、ORDER BY 句を直接指定することはできません。 TOP を使用して、意味のある順序で行を削除する必要がある場合は、サブセレクト ステートメントで ORDER BY 句を指定して TOP を使用する必要があります。 例については、後の「例」のセクションを参照してください。
パーティション ビューに対して DELETE ステートメントで TOP を使用することはできません。
ロック動作
既定では、DELETE ステートメントは、常に、そのステートメントで変更するテーブルについて排他 (X) ロックを獲得し、トランザクションが完了するまでそのロックを保持します。 排他 (X) ロックをかけたトランザクション以外はデータを変更できませんが、NOLOCK ヒントまたは READ UNCOMMITTED 分離レベルが指定されている場合に限り、読み取り操作は行うことができます。 別のロック手法を指定することで DELETE ステートメントの期間のこの既定の動作を上書きするテーブル ヒントを指定できます。ただし、このヒントは、経験豊富な開発者およびデータベース管理者が最後の手段としてのみ使用することを推奨します。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
ヒープから行を削除するときには、データベース エンジンによって、操作に行またはページ ロックが使用されることがあります。 その結果、削除操作で空になったページがヒープに割り当てられたままになります。 空のページの割り当てが解除されないと、データベース内の他のオブジェクトで該当の領域を再利用できなくなります。
ヒープ内の行を削除し、ページの割り当てを解除するには、次のいずれかの方法を使用します。
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、FROM、カーソル |
リモート テーブルから行を削除する |
リンク サーバー、OPENQUERY 行セット関数、OPENDATASOURCE 行セット関数 |
ヒントを使用してクエリ オプティマイザーの既定の動作を上書きする |
テーブル ヒント • クエリ ヒント |
DELETE ステートメントの結果をキャプチャする |
OUTPUT 句 |
基本構文
このセクションの例では、最低限必要な構文を使用して DELETE ステートメントの基本機能を示します。
A. WHERE 句を指定せずに DELETE を使用する
次の例では、削除する行数を制限する WHERE 句が指定されていないため、SalesPersonQuotaHistory テーブルからすべての行が削除されます。
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO
削除する行数を制限する
このセクションの例では、削除する行数を制限する方法を示します。
A. WHERE 句を使用して行セットを削除する
次の例では、StandardCost 列の値が 1000.00 より大きいすべての行を ProductCostHistory テーブルから削除します。
USE AdventureWorks2012;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO
次の例では、より複雑な WHERE 句を示します。 WHERE 句では、削除する行を決定するために満たす必要がある 2 つの条件を定義しています。 StandardCost 列の値が 12.00 から 14.00 までの範囲に含まれ、SellEndDate 列の値が NULL であることが必要です。 この例では、削除される行数を返す @@ROWCOUNT 関数から値も出力されます。
USE AdventureWorks2012;
GO
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));
B. カーソルを使用して削除する行を決定する
次の例では、my_cursor というカーソルを使用している 1 行を EmployeePayHistory テーブルから削除します。 この操作では、カーソルから現在フェッチされている 1 行だけが削除されます。
USE AdventureWorks2012;
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
C. 1 つのテーブルへの結合およびサブクエリを使用して、別のテーブルの行を削除する
次の例では、1 つのテーブル内の行を、別のテーブルのデータに基づいて削除する 2 つの方法を示します。 どちらの例でも、SalesPerson テーブルに格納されている今年に入ってからの売り上げに基づいて、SalesPersonQuotaHistory テーブルから行を削除します。 最初の DELETE ステートメントは ISO 互換のサブクエリ ソリューションを示しています。また、2 つ目の DELETE ステートメントは、2 つのテーブルを結合する Transact-SQL FROM 拡張機能を示しています。
-- SQL-2003 Standard subquery
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension
USE AdventureWorks2012;
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
A. TOP を使用して削除する行数を制限する
DELETE ステートメントで TOP (n) 句を使用した場合、ランダムに選択される n 行に対して削除操作が実行されます。 次の例では、納期が 2006 年 7 月 1 日より早い 20 行を PurchaseOrderDetail テーブルからランダムに選択して削除します。
USE AdventureWorks2012;
GO
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
TOP を使用して、意味のある順序で行を削除する必要がある場合は、サブセレクト ステートメントで ORDER BY を指定して TOP を使用する必要があります。 次のクエリでは、納期が早いものから 10 行を PurchaseOrderDetail テーブルから削除します。 10 行だけを確実に削除するために、サブセレクト ステートメントではテーブルの主キーの列 (PurchaseOrderID) を指定しています。 サブセレクト ステートメントで非キー列を指定すると、指定した列に重複する値が含まれる場合、10 行以上の行が削除される可能性があります。
USE AdventureWorks2012;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
(SELECT TOP 10 PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderDetail
ORDER BY DueDate ASC);
GO
リモート テーブルから行を削除する
このセクションの例では、リンク サーバーまたは行セット関数を使用してリモート テーブルを参照し、リモート テーブルから行を削除する方法を示します。 リモート テーブルとは、別のサーバーまたは別の SQL Server インスタンスにあるテーブルのことです。
A. リンク サーバーを使用してリモート テーブルからデータを削除する
次の例では、リモート テーブルの行を削除します。 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'AdventureWorks2012';
GO
-- Specify the remote data source using a four-part name
-- in the form linked_server.catalog.schema.object.
DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16;
GO
B. OPENQUERY 関数を使用してリモート テーブルからデータを削除する
次の例では、OPENQUERY 行セット関数を指定してリモート テーブルから行を削除します。 この例では、前の例で作成したリンク サーバー名を使用します。
DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO
C. OPENDATASOURCE 関数を使用してリモート テーブルからデータを削除する
次の例では、OPENDATASOURCE 行セット関数を指定してリモート テーブルから行を削除します。 server_name または server_name\instance_name の形式で、データ ソースの有効なサーバー名を指定します。
DELETE FROM OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 17;'
DELETE ステートメントの結果をキャプチャする
A. DELETE を OUTPUT 句と共に使用する
次の例では、DELETE ステートメントの結果をテーブル変数に保存する方法を示します。
USE AdventureWorks2012;
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
B. OUTPUT を DELETE ステートメント内で <from_table_name> と共に使用する
次の例では、DELETE ステートメントの FROM 句で定義された検索条件に基づいて、ProductProductPhoto テーブルの行を削除します。 OUTPUT 句では、削除されるテーブルの列 (DELETED.ProductID、DELETED.ProductPhotoID)、および Product テーブルの列を返します。 これは FROM 句で削除する行を指定するときに使用されます。
USE AdventureWorks2012;
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