SQLiteでMySQLのON DUPLICATE KEY UPDATEみたいなupsertをやりたい

MySQLのINSERT ... ON DUPLICATE KEY UPDATE ...構文って、すごく便利ですよね。↓みたいなケースはよく発生すると思うのですが、一つのSQLで書けてしまいます。

• レコードが存在しなければ、INSERT。
• レコードが既に存在していれば、指定したカラムだけUPDATE。

愚直にやるとSELECTして実在確認してから、INSERTかUPDATEか出しわける、みたいなことになるので、必ず2回のクエリになってしまうところを、1回のクエリでエレガントに書けます。

SQLiteでも似たようなことがしたくて調べたところ、INSERT OR REPLACE ...構文がありました。しかしちょっとやりたいことと違うみたい。

REPLACE構文は、UNIQUE制約/主キーのダブりでINSERTが失敗した場合、邪魔になったレコードを削除してから新しくINSERTしようとします。これだと、AUTO INCREMENTなキー列の値が変わってしまいます。

SQLite Query Language: ON CONFLICT clause

なので、ON DUPLICATE KEY UPDATEと同じことをしたい場合は、2回のクエリで書くしかないみたいです。といってもSELECTしなくてもOK。

1. とりあえずINSERT OR IGNORE ...を投げる。
2. ホスト言語側でINSERT文の影響行数を調べて、0行だったら(=INSERTに失敗していれば)UPDATE ...を実行する。

この書き方なら、新規の時は1クエリで済むので、愚直にSELECTするよりは効率がよさそうです。

あー、もしくはこっちでもいいかもな。先にUPDATEすれば、更新の方が新規登録より多いようなケースに最適化されそう。

1. とりあえずUPDATE ...を投げる。
2. ホスト言語側でUPDATE文の影響行数を調べて、0行だったら(=UPDATEに失敗していれば)INSERT ...を実行する。

まあSQLiteはネットワーク越しに操作することはないので、クエリの数が多少増えても気にならないのかもしれません。