Oracle SQL データの削除:DELETE文の基礎
- EXISTS句によるDELETE処理
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
DELETE文の構文
DELETE FROM <表名>
[ WHERE <検索条件> ]
DELETE文では、WHERE句の検索条件に合致する行を削除します。
WHERE句は必ずしも必要ではなく、その場合には指定された表の全ての行が削除されます。
DELETE文は行単位での削除を行うのであり、ある行のある列のデータを削除する事は出来ません。
列のデータの削除はUPDATE文でNULL等を設定するしかありません。
DELETE文ではいきなりデータ行を削除しますが、COMMIT文の実行を行うまで実際のデータベースには
反映されません。つまり削除処理を解除する場合にはROLLBACK文を実行します。
以下にDELETE文の実行例を示します。
SQL> SELECT * FROM TT_売上明細
2 WHERE 売上番号 = 6;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
6行が選択されました。
SQL> DELETE FROM TT_売上明細
2 WHERE 売上番号 = 6
3 AND 明細番号 = 6;
1行が削除されました。
SQL> SELECT * FROM TT_売上明細
2 WHERE 売上番号 = 6;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
SQL> DELETE FROM TT_売上明細;
15行が削除されました。
SQL> SELECT * FROM TT_売上明細;
レコードが選択されませんでした。
1番目のDELETE文はWHERE句の条件付のデータ削除を行い、2番目のDELETE文は全ての行を削除します。
この状態で削除の取り消しを行う場合は以下の様になります。
SQL> ROLLBACK;
ロールバックが完了しました。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
16行が選択されました。
ROLLBACK文を実行後はTT_売上明細の状態が元に戻ったことがわかると思います。
クエリーの結果によるDELETE (DELETE WHERE SELECT)
データの更新でも説明しましたが、DELETE文でもUPDATE文の様なクエリー結果により削除を行うことができます。
以下の例では、TT_売上明細の中からTM_商品の仕入単価が10万円以下のものを削除することを考えています。この処理自体にはあまり意味があるものではありません。取り敢えず、現状のTT_売上明細の一覧を見てみます。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
1 1 1 2 *****
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 2
8 1 8 3
8 2 9 1
9 1 7 2
9 2 9 1
17行が選択されました。
以下のDELETE文は、1行目で削除するテーブルを宣言し、2行目でWHERE句で条件付けを宣言しています。3行目以降は、その条件付けを行う商品コードの副問い合わせを宣言するSELECT文です。
SQL> DELETE FROM TT_売上明細 TD
2 WHERE TD.商品コード IN (
3 SELECT 商品コード FROM TM_商品
4 WHERE 仕入単価 <= 100000
5 );
9行が削除されました。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
1 1 1 2 *****
1 2 4 1
2 2 5 2
3 1 3 3
4 1 6 1
5 1 8 3
6 1 8 1
8 1 8 3
8行が選択されました。
上記DELETE文の3,4行目を抜き出して実行すると以下の様になり、実際に一覧される商品コードを持つTT_売上明細が削除されていることがわかると思います。尚、このWHERE句のINは割と重宝するもので副問い合わせの結果が1件もデータを返さなくても、”0行が削除されました。”として処理を正常終了してくれます。
SQL> SELECT 商品コード FROM TM_商品
2 WHERE 仕入単価 <= 100000;
商品コード
----------
100
2
7
9
10
11
12
7行が選択されました。
また、削除された内容をロールバックし上記のDELETE文をSELECT文にすれば削除対象となるデータの一覧が取れます。DELETE文を実行する前に、SELECT文を実行してみて実際に削除されるデータがどういったものになるのかを前もって確認することは必要だと思います。
SQL> ROLLBACK;
ロールバックが完了しました。
SQL> SELECT * FROM TT_売上明細 TD
2 WHERE TD.商品コード IN (
3 SELECT 商品コード FROM TM_商品
4 WHERE 仕入単価 <= 100000
5 );
売上番号 明細番号 商品コード 売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
2 1 2 1
2 3 11 1
4 2 7 2
5 2 9 2
6 2 9 1
6 3 10 2
8 2 9 1
9 1 7 2
9 2 9 1
9行が選択されました。
クエリーの結果によるDELETEその２ (DELETE NOT EXISTS)
今回は、削除するテーブルとそれに紐付くテーブルの関連するクエリー結果を用いて削除を行う例を示します。システムのデバッグが進めていくと、売上データと売上明細データに整合性が取れない場合が発生したりします。 （整合性が取れないのはプログラムのバグが原因なのですが・・・） TT_売上とTT_売上明細で、明細にはデータがあるがヘッダデータの方にはデータが無いものが存在すると仮定します。 以下のSQLで検索すると、TT_売上．売上番号のところが空白表示になる部分が確認できます。
SQL> SELECT TH.売上番号,TD.売上番号 ,TD.明細番号
2 FROM TT_売上 TH ,TT_売上明細 TD
3 WHERE TH.売上番号(+) = TD.売上番号
4 ORDER BY TD.売上番号,TD.明細番号;
売上番号 売上番号 明細番号
---------- ---------- ----------
1 1 1
1 1 2
2 2 1
2 2 2
2 2 3
3 3 1
4 4 1
4 4 2
5 5 1
5 5 2
6 6 1
6 6 2
6 6 3
8 1
8 2
9 1
9 2
17行が選択されました。
上の売上番号が８，９のTT_売上明細のデータを削除するのですが、対象となるデータを先ずSELECTで検索してみます。
SQL> SELECT TD.売上番号 ,TD.明細番号
2 FROM TT_売上明細 TD
3 WHERE NOT EXISTS (
4 SELECT * FROM TT_売上 TH
5 WHERE TH.売上番号 = TD.売上番号
6 );
売上番号 明細番号
---------- ----------
8 1
8 2
9 1
9 2
WHERE句のNOT EXISTSでTT_売上明細には存在するが、TT_売上には存在しない売上番号を検索しています。
さて、削除ですがSELECTの行の部分をDELETEに置き換えれば、削除SQLができますので、以下の結果をご覧下さい。
SQL> DELETE FROM TT_売上明細 TD
2 WHERE NOT EXISTS (
3 SELECT * FROM TT_売上 TH
4 WHERE TH.売上番号 = TD.売上番号
5 );
4行が削除されました。
SQL> SELECT TH.売上番号,TD.売上番号 ,TD.明細番号
2 FROM TT_売上 TH ,TT_売上明細 TD
3 WHERE TH.売上番号(+) = TD.売上番号
4 ORDER BY TD.売上番号,TD.明細番号;
売上番号 売上番号 明細番号
---------- ---------- ----------
1 1 1
1 1 2
2 2 1
2 2 2
2 2 3
3 3 1
4 4 1
4 4 2
5 5 1
5 5 2
6 6 1
6 6 2
6 6 3
13行が選択されました。
売上番号の８，９が削除されたことが確認できます。 今回は対象が存在しない場合で、NOT EXISTS を使いましたが、逆の処理を行う場合は、EXISTSのみを使用します。
