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

2021年9月13日月曜日

SQLite3 UPDATE に関するまとめ

ver 3.36.0 時点

初級

中級

上級

古いバージョンの情報

参考URL

Posted by at
Labels: ,

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

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

2021年7月24日土曜日

SQLite 3 テーブルのデータを更新する (update)

概要

SQLite3におけるUpdate文の基本的な使い方。
ちなみにリレーショナルデータベースではデータを書き換えることを 一般的に「変更」ではなく「更新」と言う。

構文

基本文は2種類あるが、どちらも処理は一緒。 


update
    table_name
set
    column_name1 = value1
    , column_name2 = value2
    , column_name3 = value3 ...
;

もしくは


update
    table_name
set (
    column_name1
    , column_name2
    , column_name3 ...
) = (
    value1
    , value2
    , value3 ...
);

実行例

環境
  • Windows 10 64bit
  • SQLite3 (3.36.0) Command-Line Shell
サンプルテーブル(product)
product_id
primary key 		name quantity
1 tomato 100
2 potato 100
3 pumpkin 50

以下は「product」テーブルの「name」と「quantity」を書き換えるSQL。 書き方は2通りあるがどちらも同じ結果になる。 


update
    product
set
    name       = 'cassava'
    , quantity = 1
;
                    

update
    product
set (
    name
    , quantity
) = (
    'cassava'
    , 1
);
結果(product)
product_id
primary key 		name quantity
1 cassava 1
2 cassava 1
3 cassava 1

条件を指定して更新する

上記の例のままだと、テーブル内全てのデータが更新されてしまう。 特定の行だけを更新する場合は「where」句を使用して条件を指定する。

構文

update
    product
set
    column_name1 = value1
    , column_name2 = value2
    , column_name3 = value3 ...
where
    column_name4 = value4
    and column_name5 = value5
    and column_name6 = value6 ...
                    

update
    product
set (
    column_name1
    , column_name2
    , column_name3 ...
) = (
    value1
    , value2
    , value3 ...
)
where
    column_name4 = value4
    and column_name5 = value5
    and column_name6 = value6 ...

実行例

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

以下は「product」テーブルの「id」カラムが「1」のレコードを更新するSQL。 書き方は2通りあるがどちらも同じ結果になる。 


update
    product
set
    name = 'cassava'
    , quantity = 1
where
    id = 1
;
                    

update
    product
set (
    name
    , quantity
) = (
    'cassava'
    , 1
)
where
    id = 1
;
結果(product)
product_id
primary key 		name quantity
1 cassava 1
2 potato 100
3 pumpkin 50

参考URL

Posted by at
Labels: ,

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

2019年8月1日木曜日

SQLite3 SelectしたデータでUpdateする(～ver 3.29.0)

概要

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

2020.10.16 追記

バージョン 3.33.0 で「update～from」構文がサポートされた。 より簡単にselectしたデータでupdateすることが可能になっている。

詳しくは以下のURLより。

SQLite3 Selectした結果でUpdateする (update from)
UPDATE FROM (英語) 公式のUPDATE～FROMに関するドキュメント

以下はver 3.33.0 未満で作業したい場合のコマンド例

環境

  • Windows 10 64bit
  • SQLite3 (3.24.0) Command-Line Shell

insert ～ select ～ on conflict

selectした結果を使ってupsertする手順のうち、updateのみを行う。
SQLite3 でレコードがあれば更新、なければ新規挿入する (on conflict)

以下の点、注意。

  • 文法の都合上「where true」が必要
  • 主キーなどが重複した場合、select の結果はexcludedという一時テーブルに格納される。
    update する際はexcludedのデータを利用する。
  • 主キーが重複しないレコードは新規挿入になってしまうため、 updateだけを行いたい場合はselect文で調整する。 


inert into
    table_01
select
    *
from
    table_02
where true
on conflict(column01)
do update
    set
        column_02 = excluded.colum_02
        , column_03 = excluded.column_03
;

update ～ select

サブクエリでselect したデータを使って update する。
ポイントは

  • サブクエリのWhere句で二つのテーブルのキーを一致させる
  • メインクエリのWhere句で対象のレコードを指定する


update
    table_01
set
    column_02 = (
        select
            table_02.column05
        from
            table_02
        where
            table_02.column04 = table_01.column_01
    )
where
    table_01.column_01 = XXXX
;

参考URL

Posted by at
Labels: , , , ,
登録: 投稿 (Atom)