2021年8月22日日曜日

SQLite3 JoinしたテーブルのデータでUpdateしたい場合

概要

2つ以上のテーブルを結合し、結合できたデータでUpdateをかけたいことがある。 その場合、「update from」構文を使えば他のテーブルのデータを使ってUpdateすることができる。

構文

以下のような「update from」構文にすれば結合したテーブルのデータを使うのと同じ意味になる。 更新対象のテーブルと結び付けるための条件はon句ではなく where句に記載しなければならないことに注意する。 


update
    table1
set
    column1 = A.column2
    , column3 = A.column4 ...
from
    table2 A
where
    table1.column5 = A.column6
    and table1.column7 = A.column_name8 ...

実行例

環境
  • Windows 10 64bit
  • SQLite3 (3.36.0) Command-Line Shell
サンプルテーブル(product)
product_id name quantity
1 tomato 100
2 potato 100
3 pumpkin 50
サンプルテーブル(new_data)
create_date product_id quantity
2020-10-14 2 30
2020-10-14 3 100
productテーブルをnew_dataテーブルのデータで更新する

update
    product
set
    quantity = new_data.quantity
from
    new_data
where
    product.product_id = new_data.product_id
結果(product)
product_id
primary key 		name quantity
1 tomato 100
2 potato 30
3 pumpkin 100

where句にはテーブル名を指定すること

where句でカラムを指定する際、更新対象のテーブル名を省略せずに記載する。 例えば以下のようにwhere句を指定するとエラーになる。 


sqlite> update
   ...>     product
   ...> set
   ...>     quantity = new_data.quantity
   ...> from
   ...>     new_data
   ...> where
   ...>    product_id = new_data.product_id;
Error: ambiguous column name: product_id         
                    

sqlite> update
   ...>     product A
   ...> set
   ...>     A.quantity = B.quantity
   ...> from
   ...>     new_data B
   ...> where
   ...>    A.product_id = B.product_id;
Error: near "A": syntax error

さらに結合するテーブルを増やす場合

from句の後にselect文と同じように「join～on～」を使えばいい。 


update
    table1
set
    column1 = table2.column2
    , column3 = table3.column_name4 ... 
from
    table2
    inner join
        table3
        on
            table2.column5 = table3.column6
            and table2.column7 = table3.column8 ...
    left join
        table4
        on
            table3.column9 = table4.column10
            or table4.column11 = "XXX" ...
where
    table1.column12 = table2.column13
    and table1.column14 = table3.column15 ...

実行例

環境
  • Windows 10 64bit
  • SQLite3 (3.36.0) Command-Line Shell
サンプルテーブル
product
product_id product_name
1 tomato
2 potato
3 pumpkin
new_data
create_data product_id price
2021-08-01 2 30
2021-08-01 3 100
receipt
user_id product_name price create_date
0001 tomato 98 2021-07-31
0001 pumpkin 55 2021-07-31
0002 tomato 102 2021-08-01
0003 potato 120 2021-08-01

「product_name」と「create_date」を条件に 「receipt」テーブルの「price」を 「new_data」テーブルの「price」でアップデートする。 


update
    receipt
set
    price = product.quantity
    
from
    product 
    inner join
        new_data
        on
            product.product_id = new_data.product_id
where
    receipt.product_name = product.product_name
    and receipt.insert_date = new_data.create_date
;

結果

receipt
user_id product_name price create_date
0001 tomato 98 2021-07-31
0001 pumpkin 55 2021-07-31
0002 tomato 102 2021-08-01
0003 potato 100 2021-08-01

同じ条件のデータが複数になった場合

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 
    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句で指定した項目でユニークになるよう注意すること。

サブクエリの方が使いやすい

from句で指定するデータは、select文のサブクエリを作った方がデバッグやリファクタリングもしやすいのでおすすめ。
SQLite 3 Selectした結果でUpdateする (update from)

参考URL

Posted by at
Labels: ,