2021年9月4日土曜日

SQLite3 updateした行の内容を取得する

概要

update文実行時に、実際に更新したレコード(行)の内容を確認するSQL。 「returning」構文を使用する。

where句を指定して意図したレコードが更新されているかを確認する時や アプリケーションの開発時に役に立ちそう。

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

構文


update table_name
set
    column_name1 = value1
    , column_name2 = value2
    , column_name3 = value3 ...
where
    column_name4 = value4
    and column_name5 = value5 ...
returning
    column_name1
    , column_name2
    , column_name3 ...
;

実行例

以下のテーブルの「id=2」のレコードを更新して、 対象となったレコードの内容を取得する。

サンプルテーブル(product)
product_id name quantity
1 tomato 80
2 potato 100
3 pumpkin 50 

sqlite> -- # 1.
sqlite> update
   ...>     product
   ...> set
   ...>     quantity = 200
   ...> where
   ...>     product_id = 2
   ...> returning
   ...>     name
   ...>     , quantity
   ...> ;
potato|200

sqlite> -- # 2.
sqlite> select * from product;
1|tomato|80
2|potato|200
3|pumpkin|50
  1. returning構文を使うとupdateされたレコードが表示される
  2. テーブルも正しく更新されている

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


sqlite> update
   ...>    product
   ...> set
   ...>    quantity = 200
   ...> where
   ...>    product_id = 2
   ...> returning
   ...>    *
   ...> ;
2|potato|200

returning で取得するカラムに別名をつけることもできる。 


sqlite> .mode box
sqlite> .headers on
sqlite> update
   ...>    product
   ...> set
   ...>    quantity = 200
   ...> where
   ...>    product_id = 2
   ...> returning
   ...>    name       as column_name1
   ...>    , quantity as column_name2
   ...> ;
┌──────────────┬──────────────┐
│ column_name1 │ column_name2 │
├──────────────┼──────────────┤
│ potato       │ 200          │
└──────────────┴──────────────┘

利用上の制限

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

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

参考URL

Posted by at
Labels: , ,