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> DELTE 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: , ,