DELETE ステートメントは、SQL Server データ テーブルから行を削除するために使用されます。 この記事では、DELETE ステートメントの使用方法について説明します。 いくつかのベスト プラクティス、制限事項、およびいくつかの例について説明します。
この記事は一連の記事の 4 つ目の記事です。 SQL Server データ変更ステートメント入門を読んで、最初から始めることができます。
このレッスンのすべての例は、Microsoft SQL Server Management Studio と AdventureWorks2012 データベースをベースにしています。 これらの無料のツールを使用するには、私のガイド「Getting Started Using SQL Server」
Before we Begin
この記事では、例にAdventureWorksデータベースを使用していますが、例をよりよく説明するために、データベース内で使用する例のテーブルを作成することにしました。
また、最初に次のINSERTステートメントを使用して、テーブルにいくつかのデータを入力してみましょう：
WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)AS (SELECT S.FirstName + ' ' + S.LastName, S.SalesLastYear, S.City ,NEWID()FROM Sales.vSalesPerson SWHERE S.SalesLastYear > 1000000)INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName, SalesLastYear, City, rowguid FROM topSalesPerson
INSERTステートメントの詳細については、記事「INSERTステートメントの紹介」を参照してください。
DELETEステートメントの基本構造
DELETEステートメントは、列の値を変更するために使用されます。
DELECTステートメントの一般的なフォーマットは次のとおりです。
単純な例 – すべての行を削除する
WHERE句なしでDELETEを使用すると、テーブルからすべての行が削除されます。 もし、esqlSalesPerson からすべての行を削除したい場合、次のように実行できます。
DELETEFROM esqlSalesPerson
この例では、DELETE コマンドをトランザクションでラップし、例のデータベースに行を繰り返し挿入しないことをお勧めします。 トランザクションをロールバックするので、削除操作は永続的ではありません。
単純な例 – 1 つの行の削除
より現実的な使用例として、1 つの行を削除することがあります。
主キーを知っていれば、主キーは一意であり、各行を明確に識別できるため、問題ありません。
DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095
すべての営業担当者をリストアップする Web アプリケーションがある場合、グリッドには営業担当者のフルネームと昨年の売上高が含まれますが、グリッドにはその営業担当者 ID も隠されていると想像できます。
ユーザーがグリッド上の行を選択し、その行を削除することを選択すると、アプリケーションはその行の隠された主キーを取得し、データベースに削除コマンドを発行します。 昨年の売上が$2,000,000以上のセールスマンだけを残しておきたいのです。
このテーブルには、この金額未満の人が含まれているので、以下を実行して削除する必要があります：
DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00
以下のコマンドを実行して、トランザクション内でスクリプトを試すことができます：
BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK
以下はスクリプトの実行結果です：
青い矢印はもともと13レコードあり、赤いのは売上200万ドル以上の残りを示しています。
より複雑なフィルタリング条件を作成することもできます。 後ほど、サブクエリの使い方を紹介します。
DELETEステートメントの使用に関する考察
テーブル内のすべての行を削除するには、TRUNCATE TABLEを使用します。 これは変更をログに記録するので、DELETEよりはるかに高速です。 しかし、重要な違いがあります（下記の「DELETE は TRUNCATE ではない！」を参照）
また、@@ROWCOUNT 関数を使用して、何行が削除されたかを知ることができることも忘れないでください。 エラーが発生した場合、行は削除されません。
削除ステートメントが失敗する理由はたくさんあります。
- 孤児の行 – FOREIGN KEY 制約が parentTable と childTable のような 2 つのテーブル間で定義されている場合、親テーブルの行を DELETE すると、その親に関連する childTable 行が存在する場合はエラーになります。 これを回避するには、まず対応する子テーブルの行を削除し、次に親テーブルの行を削除します。
- 算術エラー – 式の評価でゼロ除算などの算術エラーが発生すると、DELETE はキャンセルされ、行は削除されません。 これは、DELETE トランザクションが完了するまで、他のクエリがテーブルのデータを変更できないことを意味します。
データを読み取ることはできますが、NOLOCK ヒントを使用するか、コミットされていない読み取り分離レベルを使用する必要があります。 テーブルから1つまたは複数の行を削除するにはDELETEを使用してください。 テーブルを初期状態に戻す必要がある場合など、特別な状況でのみ TRUNCATE を考慮すべきです。
多くの人が DELETE と TRUNCATE を混同しています。 不明な場合は、「SQL Server における切り捨てと削除の違いとは」 の記事を読むことをお勧めします。
複雑な例 – サブクエリを使用した DELETE
より複雑な削除文を作成することも可能です。 SELECT ステートメントの WHERE 句に書くことができるほぼすべての句を DELETE ステートメントに書くことができ、WHERE 句のサブクエリも含まれます。 私たちのテーブルは City を持ちますが、country を持ちません。
BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK
Please Note, I wrapped the example in a transaction so I won’t permanently delete my test data.
The subquery is colored blue.This can get around this by a subquery.
BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK
サブクエリーを使用することで、これを回避できます。
この DELETE 文の仕組みは次のとおりです。
- 米国に住むすべての人の個別の都市を検索します。
- このリストに都市が含まれる販売員を DELETE します。 2番目のステップでは、WHERE句ごとに行を削除します。