2021年7月10日土曜日

SQLite 3 deleteした行の内容を確認する (returning)

概要

delete文実行時に削除対象となったレコード(行)の内容を確認するSQL。 「returning」構文を使用する。

以下のような状況で役に立ちそう。

  • 大量のレコードを持つテーブルにdelete文を実行した際、 意図したレコードが削除されたか確認が楽になる
  • トランザクション管理のrollback機能と組み合わせれば SQLの開発時にテストやデバッグが捗る。

「returning」構文はSQLite の ver 3.35.0 から利用可能。

構文


delete from table_name
where
    column_name = value ...
returning
    column_name1
    , column_name2 .... 
;

実行例

環境
  • Windows 10 64bit
  • SQLite3 (3.35.5) Command-Line Shell
サンプルテーブル(product)
id name quantity
1 tomato 100
2 potato 120
3 pumpkin 50

「id=2」のレコードを削除し、削除された行を確認してみる。 


sqlite> select * from product;
1|tomato|100
2|potato|120
3|pumpkin|50

sqlite> -- # 1.
sqlite> delete from product
    ...> where
    ...>     id = 2
    ...> returning
    ...>     id
    ...>     , name
    ...>     , quantity
    ...> ;
2|potato|120

sqlite> -- # 2.
sqlite> select * from product;
1|tomato|100
3|pumpkin|50
  1. returning構文を使うと削除対象になったレコードが表示される
  2. テーブルからは正しく削除されている

returningで取得するカラムに「*」を指定することもできる。 


sqlite> delete from product
    ...> where
    ...>     id = 2
    ...> returning
    ...>     *
    ...> ;
2|potato|120

returningで取得する内容に別名をつけることもできる。 


sqlite> .mode box
sqlite> .headers on
sqlite> delete from product
    ...> where
    ...>     id = 2
    ...> returning
    ...>     id as column1
    ...>     , name as column2
    ...>     , quantity as column3
    ...> ;
┌─────────┬─────────┬─────────┐
│ column1 │ column2 │ column3 │
├─────────┼─────────┼─────────┤
│ 2       │ potato  │ 120     │
└─────────┴─────────┴─────────┘

SQLite 3 コマンドラインツールでselectの結果を見やすくする
SQLite 3 コマンドラインツールでカラム名を表示する「.headers」について

利用上の制限

SQLite 3.35.5 の時点で「returning」構文に以下のような制限がある。
これは将来のバージョンで改善される可能性がある。

  • 仮想テーブルへのdelete文では使用できない。
  • 素のdelete文でのみ使用可能。トリガー内のdelete文では使用出来ない。
  • サブクエリでは使用出来ない。 例えば「delete～returning～」で取得したレコードをwhere句に使って 他のテーブルからデータをselectする、といったことは出来ない。
  • returning文で取得したレコードをソートすることは出来ない。
  • delete文を含むトリガーが設定されているテーブルに対し「delete～returning～」を実行しても、 トリガーが削除した分のレコードは取得出来ない。
  • returningで取得したレコードには集計関数やwindow関数を使用できない。

参考サイト

Posted by at
Labels: , ,