2020年10月14日水曜日

SQLite3 Selectした結果でUpdateする (update from)

概要

selectしたデータを使って updateをするためのSQL。
selectとupdateを別々のSQLで実行するのではなく、同時に実行することができる。

専用の「update～from」構文がsqlite のバージョン3.33.0 から利用可能。

バージョン3.32.0未満場合は以下を参照のこと。
SQLite3 SelectしたデータでUpdateする(～ver 3.29.0)

構文

「update～from」の構文は以下の通り。

selectで取得する内容はサブクエリと同じように書けばいい。 更新先のテーブルの内容を含んでいてもいい。 


update
    tableA
set
    column1   = B.column2
    , column3 = B.column4 ...
from (
    select
        column2
        , column4 ...
    from
        tableB
) as B
where
    tableA.column5 = B.column6

実行例

環境
  • Windows 10 64bit
  • SQLite3 (3.33.0) Command-Line Shell
サンプルテーブル(product)
product_id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 100
3 pumpkin 50 (Comment...)
サンプルテーブル(new_data)
create_date product_id quantity remark
2020-10-14 2 30 new comment 01
2020-10-14 3 100 new comment 02
2020-10-14 4 200 new comment 03
productテーブルをnew_dataテーブルのデータで更新する

update
    product
set
    quantity = A.quantity
    , remark = A.remark
from (
    select
        product_id
        , quantity
        , remark
    from
        new_data
) as A
where
    product.product_id = A.product_id
結果(product)
product_id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 30 new comment 01
3 pumpkin 100 new comment 02

使われないデータがあってもエラーにならない

上記の例だとproductテーブルにはproduct_id=4のデータがないため、 new_dataテーブルのproduct_id=4のデータは反映されない。 この際エラーメッセージ等は表示されないため、大量のデータを処理した時に気づきにくい。 厳密に処理するのなら、事前にチェックするか外部キー制約等を利用する必要がある。

selectした結果でupdateとinsertを一括で行いたい場合は 「replace」文や「insert ～ on conflict」構文を利用する。

サブクエリで同じ条件のデータが複数になった場合

where句で指定する条件が同じになるデータが複数になってしまった場合、 どのデータを更新先のテーブルに反映させるかを選択することが 出来ない。

サンプルテーブル(product)
product_id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 100
3 pumpkin 50 (Comment...)
サンプルテーブル(new_data)
create_date product_id quantity remark
2020-10-14 2 30 new comment 01
2020-10-14 3 100 new comment 02
2020-10-15 3 200 new comment 03 

update
    product
set
    quantity = A.quantity
    , remark = A.remark
from (
    select
        product_id
        , quantity
        , remark
    from
        new_data
) as A
where
    product.product_id = A.product_id
結果(product)
product_id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 30 new comment 01
3 pumpkin 200 new comment 03

上記の例だと、new_dataテーブルにproduct_id=3 のデータが2行あるが、 反映されるデータを指定することはできず、いずれかのデータが自動的に反映される。
サブクエリの内容はwhere句で指定した項目でユニークになるよう注意すること。

参考URL

Posted by at
Labels: ,