SQLite 備忘録: returning

ラベル returning の投稿を表示しています。すべての投稿を表示

2021年9月4日土曜日

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

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

returning構文を使うとupdateされたレコードが表示される
テーブルも正しく更新されている

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

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
SQLite Query Language: UPDATE(英語)
公式のUPDATE構文に関するドキュメント
https://www.sqlite.org/lang_update.html
RETURNING(英語)
公式のReturning構文に関するドキュメント
https://www.sqlite.org/lang_returning.html

2021年7月18日日曜日

SQLite 3 insertした行の内容を取得する (returning)

INSERTのまとめ

概要

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

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

insert～selectのようにinsertするデータを直接insert文に記載しない場合の確認。
insert～on conflict を使った時に実際に反映されたデータの確認。

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

構文


insert into table_name (
    column_name1
    , column_name2
    , column_name3 ...
)
values (
    value1
    , value2
    , value3 ...
)
returning
    column_name1
    , column_name2
    , column_name3 ...
;

実行例

以下のようなサンプルテーブルにデータを新規挿入する

「product」テーブル
カラム名	データ型	制約
id	integer	primary key
name	text	not null
quantity	integer	default 0


sqlite> -- # 1.
sqlite> insert into product (
   ...>     id
   ...>     , name
   ...>     , quantity
   ...> )
   ...> values (
   ...>     1
   ...>     , 'tomato'
   ...>     , 100
   ...> )
   ...> returning
   ...>     id
   ...>     , name
   ...>     , quantity
   ...> ;
1|tomato|100

sqlite> -- # 2.
sqlite> select * from product;
1|tomato|100

returning構文を使うとinsertされたレコードが表示される
テーブルにも正しく挿入されている

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


sqlite> insert into product (
   ...>     id
   ...>     , name
   ...>     , quantity
   ...> )
   ...> values (
   ...>     1
   ...>     , 'tomato'
   ...>     , 100
   ...> )
   ...> returning
   ...>     *
   ...> ;
1|tomato|100|

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


sqlite> .mode box
sqlite> .headers on
sqlite> insert into product (
   ...>     id
   ...>     , name
   ...>     , quantity
   ...> )
   ...> values (
   ...>     1
   ...>     , 'tomato'
   ...>     , 100
   ...> )
   ...> returning
   ...>     id as column_name1
   ...>     , name as column_name2
   ...>     , quantity as column_name3
   ...> ;
┌──────────────┬──────────────┬──────────────┐
│ column_name1 │ column_name2 │ column_name3 │
├──────────────┼──────────────┼──────────────┤
│ 1            │ tomato       │ 100          │
└──────────────┴──────────────┴──────────────┘

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

デフォルト値が入った場合も取得できる

以下はquantityカラムに値を入れず、デフォルト値が設定された場合の例。


sqlite> insert into product (
   ...>     id
   ...>     , name
   ...> )
   ...> values (
   ...>     2
   ...>     , 'potato'
   ...> )
   ...> returning
   ...>     id
   ...>     , name
   ...>     , quantity
   ...> ;
2|potato|0

利用上の制限

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

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

参考サイト

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
SQLite Query Language: INSERT(英語)
公式のINSERT構文に関するドキュメント
https://www.sqlite.org/lang_insert.html
RETURNING
公式のRETURNING構文に関するドキュメント
https://www.sqlite.org/lang_returning.html

2021年7月10日土曜日

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

DELETEのまとめ

概要

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

returning構文を使うと削除対象になったレコードが表示される
テーブルからは正しく削除されている

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関数を使用できない。

参考サイト

SQLite 公式サイト(英語)
https://www.sqlite.org/index.html
SQLite Query Language: DELETE(英語)
公式のDELETE構文に関するドキュメント
https://www.sqlite.org/lang_delete.html
RETURNING
公式のRETURNINGに関するドキュメント
https://www.sqlite.org/lang_returning.html

登録: 投稿 (Atom)

2021年9月4日土曜日

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

概要

構文

実行例

利用上の制限

参考URL

2021年7月18日日曜日

SQLite 3 insertした行の内容を取得する (returning)

概要

構文

実行例

デフォルト値が入った場合も取得できる

利用上の制限

参考サイト

2021年7月10日土曜日

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

概要

構文

実行例

環境

利用上の制限

参考サイト

ページ