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

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

2019年7月2日火曜日

SQLite3でUpsertを実行する

概要

SQLite 3 でキーか重複するレコードがあれば更新、なければ新規挿入する方法。

具体的には テーブルにレコードを新規挿入しようとして Primary key や Unique が重複するレコードが既に存在していた場合、 新規挿入をやめて update に切り替えるための構文。

以下のような処理分岐をしてくれる。

重複するレコードがある場合 既存のレコードを「更新(update)」する、もしくは何もしない(nothing)
重複するレコードがない場合 そのまま 「新規挿入(insert)」

似たような処理にreplaceがあるが、 replaceはレコード(行)を丸ごと入れ替える(delete/insert)のに対し、 upsertはカラムを指定してレコードを更新する。

公式 によると PostgreSQL の構文を利用しているとのこと。
Microsoft SQLServer や Oracle の「merge(マージ)」構文に近い使い方だが
mergeは重複の判定にUniqueカラム以外のものを指定できたりする。

実行例

「upsert」というコマンドがあるわけではなく、 on conflict文を使い キーが重複した場合の処理を指定することができる。

on conflict文は sqlite のバージョン3.24.0 から利用可能。

環境
  • Windows 10
  • sqlite3 (3.24.0) Command-Line Shell
サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

重複していたら特定のカラムを更新する

以下は「id」カラムが重複していたら新規挿入をやめて、 別の値で「quantity」カラムを更新する例。 


insert into 
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 200
3 pumpkin 50 (Comment...)

excluded テーブルを利用する

挿入しようとして、ユニークキーの重複エラーになった行は 「excluded」という一時テーブルに登録される。 update の際にこのexcludedを利用できる。

以下は「id」カラムが重複していたら 新規挿入をやめて、 挿入しようとした値で「quantity」カラムを更新する例 


insert into
    product (
        id
        , name     
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
)
on conflict(id)
do update
    set
        quantity = excluded.quantity
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 150
3 pumpkin 50 (Comment...)

重複していなければ、新規登録される

以下は「id」カラムが重複していないので、 そのまま新規挿入される例。 


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        4
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do update
    set
        quantity = '200'
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
4 potato 150

重複していた場合は何もしない、ということもできる

on conflictの後にdo nothingを記述すれば 重複していた場合は何もしない、という処理に出来る。 


insert into
    product (
        id
        , name
        , quantity
        , remark
    )
values (
        2
        , 'potato'
        , 150
        , ''
    )
on conflict(id)
do nothing
;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
行を丸ごと入れ替えるだけであれば replace を使う。

参考URL

Posted by at
Labels: , , ,

2019年6月14日金曜日

SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

概要

replace (insert or replace) 構文を使う方法のメモ。

replace を使うと Primary key や Unique で 重複するレコードを挿入しようとした場合、 以下のような処理分岐をしてくれる。

重複するレコードがある場合 既存のレコードを「削除(delete)」 してから 「新規挿入(insert)」
重複するレコードがない場合 そのまま 「新規挿入(insert)」

注意

replaceで重複したキーがあった場合の処理は「削除(delete)」「新規挿入(insert)」であり、 「更新(update)」ではない。 カラムを指定しなければデフォルト値が入るし、NOT NULL制約にもひっかかる。

カラムを指定して「更新(update)」したい場合は replaceではなくupsertを使用する。
SQLite3 で upsert する

環境

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

replace 構文の例

サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		price
default 0 		category
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

replace(insert or replace) の実行例

以下はidが重複するデータと重複しないデータを挿入するSQL。 「insert or replace」の部分は「replace」だけでもOK。 


insert or replace into product (
    id
    , name
    , quantity
    , remark
)
values (
    2
    , 'potato'
    , 150
    , ''
),(
    4
    , 'carrot'
    , 100      
    , 'Prefecture: XX'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 150
3 pumpkin 50 (Comment...)
4 carrot 100 (Prefecture : XX)

カラムを指定しないとデフォルト値になる

delete > insert であり、updateではないことを留意する。 


replace into product (
    id
    , name
)
values (
    2
    , 'potato'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 0
3 pumpkin 50 (Comment...)

not null 制約にも引っかかる


replace into product (
    id
    , quantity
)
values (
    2
    , 200
);

結果 


Error: NOT NULL constraint failed: product.name
カラムを指定してUPDATEする場合はUPSERTを利用する。
SQLite 3 で upsert する

参考・関連 URL

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