レコードがなければ追加、あれば更新を一度に実行できる『UPSERT』とは

2013年7月16日2020年9月4日

MySQLやPostgreSQL、SQLite3といったデータベースを使ったプログラミングをしていると、『データがない場合は新規追加を行い、データがある場合はその既存データを更新する』という実装をすることが頻繁にあります。

そんなときに知っておくと便利なのが『UPSERT』という概念です。

『UPSERT』とは？

UPSERTとは、『レコードがなければINSERT(新規追加)を行い、レコードがあればUPDATE(更新)を行う処理』のことです。

INSERT(追加)とUPDATE(更新)の両方の機能を併せ持っていることから、UPDATE + INSERTで、UPSERTと呼ばれています。

2013年7月18日【STOP!デスマーチ】『ブラック会社によろしく』がリアル過ぎる！

どんな時に『UPSERT』を使うのか？

データベースのテーブルにデータを追加したいと思っていて、『そのデータがあれば更新し、なければ追加する』という処理をやりたいとき。

具体的には、こんな処理をやっている場合です。

これから追加しようとしているデータがテーブルにあるかどうかをチェックするために、SELECT文を実行する。
(既存のデータがあれば)UPDATE文を実行する。
(既存のデータがなければ)INSERT文を実行する。

プログラムから見れば1つの処理で済ませたいのに、必ず2回SQLの問い合わせが発生してしまうのが問題、だと感じたときなどです。
UPSERTを使えば、1回のSQLで実行できるようになります。

『UPSERT』の使い方

『じゃあ、INSERTとUPDATEを使う代わりに、UPSERTを使えばいいんだ。』

と思うかもしれませんが、SQLで言うINSERT文やUPDATE文の代わりに、UPSERTと入れても使えません。

INSERT文やUPDATE文のように、UPSERT文というものがあるわけではなく、UPSERTの機能を持った構文は使っているデータベースの種類によって違うからです。

SQLite3でUPSERTをやりたい時は『INSERT OR REPLACE』か『REPLACE』を使う。

SQLite3でUPSERTを使いたいときは「INSERT OR REPLACE INTO」構文か「REPLACE INTO」構文を使います。

どちらの構文でも機能は同じ(※INSERT OR REPLACEの別名がREPLACE)で、INSERT(新規追加)またはUPDATE(更新)を1つのSQLで実行できます。
動作のイメージは、SQL As Understood By SQLiteに詳しく書かれています。

INSERT OR REPLACE INTOは、INSERT時にプライマリキー制約かユニーク制約で重複していれば、自動的にDELETEを実行する。
主キーに該当するレコードがあればUPDATEを行い、なければINSERTを行う。

INSERT OR REPLACE INTO(REPLACE INTO)の使い方

INSERT OR REPLACE INTO テーブル名(カラム名) VALUES (値, 値, 値…);

または、

REPLACE INTO テーブル名(カラム名) VALUES (値, 値, 値…);

MySQLでUPSERTをやりたい時は『ON DUPLICATE KEY』か『REPLACE』使う。

MySQLでUPSERTを使いたいときは「INSERT ON DUPLICATE KEY UPDATE」構文か「REPLACE」構文を使います。

INSERT ON DUPLICATE KEY UPDATEは、主キーに該当するレコードがあればUPDATEを行う。なければINSERTを行う。
REPLACEは、主キーに該当するレコードがあればDELETEを実行した後、INSERTを行う。なければINSERTを行う。

OracleでUPSERTをやりたい時は『MERGE』を使う。

Oracleの場合は『MERGE』構文を使います。
行の挿入と更新を1ステートメントで行なう(MERGE = UPDATE + INSERT)に詳しく解説されています。

普段フレームワークを使っていると、これらの処理はフレームワークが吸収してくれるのであまり意識する必要がありませんが、フレームワークを使う程ではないちょっとしたプログラムを書くときには知っておくと便利です。

2013年11月7日リアル過ぎる『ブラック会社によろしく』に待望の続編が！

プログラミングを効率化するならペアプログラミング・コードレビュー・QAサイトの活用は抑えておこう

プログラミングを効率的に学ぶなら、おすすめの方法は一人ではなく誰かと一緒にプログラミングをするということです。他の人のプログラミングを学ぶことで、自分独自のクセや苦手な部分も見えてきます。ペアプログラミングだったり、コードレビューなども行えるので、複数人でプログラミングを行うのは、プログラミングを上達させる重要なコツです。

いつも一人でコードを書いていて、同僚や先輩などがいないというときは、プログラミングのオンラインスクールCodeCampの無料体験レッスンなどを使うのが良いでしょう。このようなオンラインスクールを使うと毎日24時まで、現役エンジニアと一緒にマンツーマンでレッスンが受けられるのでプログラム初心者や、ペアプログラミング未体験の方は試してみるのも良いかと思います。

また、プログラミングは独りでする作業が多く、コードを書いてテストをして、わからない時はググッて…の繰り返しですが、なかなか問題解決するための情報が見つからないというときは、無料でプログラミングの質問に答えてくれるテラテイルなどのプログラミングQ&Aサービスを使うのが便利です。さまざまな方があなたの問題に回答してくれるので登録しておくと便利です。

週3日の副業でも月50万円稼げるのがエンジニアの圧倒的な強み

今、エンジニアは引く手あまたの完全な売り手市場です。今までは週5日働いて土・日休みなどの週休2日で働くエンジニアも多かったのですが、このような市場環境であるためフリーランスや時短で働くエンジニアも増えてきています。特にウェブ系は顕著ですね。

エンジニアは手に職があり、技術があれば世の中を渡り歩いていける職種ですから、このような強みを活かさない手はありません。同じような技術レベルでも、稼げるエンジニアとまったく稼げないエンジニアの大きな違いは『仕事の情報をどう得るか』という部分です。同レベルのスキルでも、案件情報をどう得ているかで収入に大きな差が生まれます。

なかでも、レバテックフリーランスなどには会員限定の非公開案件があり、週3日の副業で月50万円程度稼ぐことが実際に可能です。

また、ハイパフォエンジニアは、30秒の会員登録をするだけで、平均報酬80万円以上の独自案件を教えてくれます。

「こなし仕事ばかりで、最近新しい分野の勉強ができない…」、「新しいサービスやアプリのアイデアを思いついたけど、仕事が忙しくて、自分のプロジェクトに割く時間がない…」というときは、週3日で生活費をしっかり稼ぎ、残りの時間を自分のプロジェクトに充てるということも十分可能です。

エンジニアの仕事の時間単価は非常に高いです。ぜひ自分の時間を安売りせず、上記のような方法で自分の時間単価を上げ、自分の好きな技術や分野に時間を投資していきましょう。

私が、『サラリーマンを辞め、起業直後に実行した方法』はコレを使いました。プログラミングと同様、自分の人生も徹底的にリファクタリングすると効率の良いシステムになりますよ。