【プログラミング】SQLite3：データが重複したら更新か何もしない、新規のデータは追加する動作 UPSERT

2022年12月20日2023年1月15日

URLをコピーしました！

SQLite3

前回、データベース管理システムSQLite3のPRIMARY KEYとUNIQUEを勉強してみました。

REPLACE INTO

まずは「REPLACE INTO」から試してみましょう。

「REPLACE INTO」では「REPLACE INTO テーブル名(列名1, 列名2…) values(値1, 値2)…;」というように使用します。

それではそれぞれPRIMARY KEYで重複するデータがない場合とある場合、またUNIQUEで重複するデータがない場合とある場合で試してみましょう。

重複するデータがない場合

PRIMARY KEYでもUNIQUEでも重複しないように idを2、nameをSuzuki、ageを15としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(2, 'Suzuki', 15);
sqlite> SELECT * FROM table1;

実行結果
1|Sato|10
2|Suzuki|15

データが追加されました。

PRIMARY KEYで重複するデータがある場合

PRIMARY KEYだけで重複するように idを1、nameをTakahashi、ageを20としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(1, 'Takahashi', 20);
sqlite> SELECT * FROM table1;

実行結果
1|Takahashi|20
2|Suzuki|15

idが1のSato 10が書き換えられて、Takahashi 20になりました。

UNIQUEで重複するデータがある場合

UNIQUEだけで重複するようにidを3、nameをSuzuki、ageを25としてみます。

sqlite> REPLACE INTO table1(id, name, age) values(3, 'Suzuki', 25);
sqlite> SELECT * FROM table1;

実行結果
1|Takahashi|20
3|Suzuki|25

Suzukiのデータが書き換えられ、idが3、ageが25になりました。

ON CONFLICT

次にもう一つのコマンドである「ON CONFLICT」を試してみましょう。

「ON CONFLICT」の場合は、「INSERT INTO」と合わせて、「INSERT INTO テーブル名(列名1, 列名2…) values(値1, 値2…) ON CONFLICT(PRIMARY KEYかUNIQUEの列名) DO 動作;」となります。

また動作には更新する場合は「UPDATE SET 列名1=値1, 列名2=値2…;」、何もしない場合は「NOTHING」と記述します。

重複するデータがない場合

PRIMARY KEYでもUNIQUEでも重複しないように idを4、nameをTanaka、ageを30としてみます。

また「ON CONFLICT」の「PRIMARY KEYかUNIQUEの列名」はとりあえずidにしておきます。

sqlite> INSERT INTO table1(id, name, age) values(4, 'Tanaka', 30) ON CONFLICT(id) DO UPDATE SET name='Tanaka', age=30;
sqlite> SELECT * FROM table1;      

実行結果                                             
1|Takahashi|20
3|Suzuki|25
4|Tanaka|30

Tanakaのデータが追加されました。

PRIMARY KEYで重複するデータがあった場合でデータを更新する場合

PRIMARY KEYだけで重複するように idを1、nameをIto、ageを40としてみます。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Ito', 40) ON CONFLICT(id) DO UPDATE SET name='Ito', age=40;
sqlite> SELECT * FROM table1;        

実行結果                                           
1|Ito|40
3|Suzuki|25
4|Tanaka|30

idが1のTakahashi 20がIto 40に書き換えられました。

UNIQUEで重複するデータがあった場合で更新する場合

UNIQUEだけで重複するようにidを5、nameをSuzuki、ageを45としてみます。

ここでは「ON CONFLICT」の「PRIMARY KEYかUNIQUEの列名」をnameにします。

sqlite> INSERT INTO table1(id, name, age) values(5, 'Suzuki', 45) ON CONFLICT(name) DO UPDATE SET id=5, age=45;
sqlite> SELECT * FROM table1;                                                   
1|Ito|40
4|Tanaka|30
5|Suzuki|45

Suzukiのデータが書き換えられ、idが5、ageが45になりました。

PRIMARY KEYで重複するデータがあった場合で何もしない場合

PRIMARY KEYだけで重複するように idを1、nameをWatanabe、ageを50としてみます。

また「ON CONFLICT(id) DO NOTHING;」とします。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Watanabe', 50) ON CONFLICT(id) DO NOTHING;
sqlite> SELECT * FROM table1;                                                   

実行結果
1|Ito|40
4|Tanaka|30
5|Suzuki|45

確かに何も変化は起きませんでした。

UNIQUEで重複するデータがあった場合で何もしない場合

UNIQUEだけで重複するようにidを6、nameをIto、ageを55としてみます。

また「ON CONFLICT(name) DO NOTHING;」とします。

sqlite> INSERT INTO table1(id, name, age) values(6, 'Ito', 55) ON CONFLICT(name) DO NOTHING;
sqlite> SELECT * FROM table1;                                                   

実行結果
1|Ito|40
4|Tanaka|30
5|Suzuki|45

こちらも追加も書き換えもありませんでした。

ON CONFLICTのキーは複数設定できるのか？

「ON CONFLICT」を試してきて気になったのは、ON CONFLICT()の括弧に入れるキーは複数設定できるのかということです。

とりあえずやってみました。

sqlite> INSERT INTO table1(id, name, age) values(1, 'Ito', 55) ON CONFLICT(id, name) DO NOTHING;

実行結果
Error: in prepare, ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint (1)

「ON CONFLICT」で指定されたキュー（語句）が「PRIMARY KEY」や「UNIQUE」のどれにも当てはまりませんでしたといいうエラーが出ました。

よく考えてみると複数の条件の設定は無理なのではないでしょうか。

上記の例だとidもnameも重複している場合は「DO NOTHING」となると考えられますが、例えばidだけ重複している場合は、データを追加しようとします。

しかしながらidはPRIMARY KEYで重複が許されないので、そこで矛盾が生じます。

もしかしたら複数の条件を設定する別の方法があるかもしれませんが、今回は「REPLACE INTO」や「ON CONFLICT」をとりあえず使えるようになることが目的なので、ここまでにしておきましょう。

次回からはSQLite3をPythonで使っていくための勉強と練習をしていきます。