【詳しく解説】OracleにおけるUpsertの書き方(サンプル付き)

当ページのリンクには広告が含まれています。

【総まとめ】UPSERTとは?DBごとの違いは?」では、主要なDBごとの書き方をざっくり説明しました。

今回は、Oracle でのUpsertに焦点を絞り、掘り下げていきたいと思います。

参考として、DataFrameからUpsertを生成するPython関数も紹介しています。必要に応じてコピぺしてご利用下さい。

目次

Oracle は MERGE ~ で Upsertを行う

Oracle でUpsertを行う場合は、MERGE INTO ~ WHEN ~を使います。

MERGE文の構文イメージ図

MERGE INTO~に更新先(インサート先)のテーブル名、 USING ~に更新元のテーブル名、ON ~ にユニークキーを指定します。

そして、WHEN MATCHED THEN ~には キーが存在した場合の処理(通常はUpdate文)、WHEN NOT MATCHED THEN ~ には キーが存在しなかった場合の処理(通常は Insert文)を記述します。

任意の値を Upsert したい場合は、 USINGに続けて (select 値1,値2,・・・)と記述します。

検証データ

本記事のUpsert文を実際に試す場合は、あらかじめ以下のSQLを実行して下さい。

テーブル間でUpsertする

2つのテーブル同士のUpsertイメージ図

テーブル間でUpsertする場合は次のように記述します。

Oracleにおけるテーブル間でのUpsert構文のイメージ図

テーブルAとテーブルBは同じ列名が存在するため、各列名の先頭にエイリアス名(又はテーブル名)を付けて、どちらのテーブルに所属するものかを明示する必要があります。

ただし、insert (列名1,列名2,・・・)の列名については、エイリアス名を省略できます。

また、insert (列名1,列名2,・・・)の列名列挙部分は省略することが可能です。

Oracleにおけるテーブル間でのUpsert構文(省略時)のイメージ図

下記は 省略した例になります。

1行をUpsertする

1レコードのUpsertイメージ図

任意の値をUpsertしたい場合は、USING テーブル名 の代わりに、(select 列名1 AS 値1,・・・)と記述します。「1行だけの一時的なテーブルを作って、それを USINGに指定する」と考えると分かり易いでしょう。

Oracleにおける1レコード挿入時のUpsert構文のイメージ図

下記は、サンプルテーブルに (1,’2023/01/01 09:00:00′,10000,5) のデータをInsertし、レコードが既に存在していれば 数量(count)を 5にUpdateするサンプルです。

ON~に記述する結合条件が真なら MATCHED THEN ~を、偽なら NOT MATCHED THEN ~を実行するので、テーブルBとは全く異なる値でUpdateやInsertを行うことも可能です。

例えば、キーが存在しなければ Insert 時に count を 5で登録し、キーが存在すれば count を 15で Updateするという事もできます。

通常は、テーブルAとテーブルBを同じ内容にするためのUpsertですから、直接の値を記述するのではなく、列名を使って更新したい値を指定します。

Oracleにおける1レコード挿入時のUpsert構文(省略時)のイメージ図

もうお気づきかもしれませんが、このMERGE文は、USING 部分を除いて、テーブルBからテーブルAへUpsertする書き方と全く同じです。

下記は簡略化した時のサンプルですが、 USING (SELECT ~)の AS は省略しています。

複数行をまとめてUpsertする

Oracleでは、1回のInsert文で複数行(複数レコード)をまとめて登録する マルチインサート文が使えますが、Upsertについても同様のことが行えます。

ポイントは、using の部分を union でつなげるところです。こうすることによって、複数行を持つテーブルが一時的に出来たように見え、テーブル間のUpsertが実行できます。

尚、次のように1回のUpsertの中で、キーが重複するデータが存在しても、順番に処理されるだけでエラーにはなりません。この場合は後勝ちになるので、最後に実行されたInsert文の値(下記の例では15)が最終的に書き込まれ(Updateされ)ます。

キーが重複しているイメージ図

UpsertでInsertだけ行う(Updateしない)

Upsertでなければインサート、あればなのもしない動作のイメージ画像

WHEN MATCHED THEN UPDATE SET ~ の記述をしなければ、Updateされません。

Pythonのサンプル

それでは、参考例としてDataFrameの値からUpsert文を生成するPythonのサンプルプログラムについてご紹介します。

使い方のサンプル

create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけで、Merge文を生成してくれます。

尚、下記サンプルで生成したUpsert文は、冒頭に紹介したサンプルテーブルに対して実行が可能です。

関数のソースコード

以下は、関数本体のソースコードになります。数値以外のデータ(文字列、日付)はシングルクォートで括る必要があるので、簡易的に dtype が ‘object’ か否かで判断しています。

また、insert into ~ values ~ の values に渡す値に ‘nan’ が含まれている場合、NULL に置換する処理を入れています。もし更新する値の中に ‘nan’ という文字列が含まれると、NULLに変換されてしまう点にご注意下さい。

まとめ

今回は Oracle のUpsertに焦点を絞って詳細を解説しました。

Oracle でUpsertを行うには、Merge into ~ に更新先テーブル名、using ~に更新元テーブル名、on ~ に結合条件を記載します。そして、 when not matched then ~ にキーが存在しなかった処理(通常は insert文)、when matched then ~ にキーが存在した時の処理(通常はUpdate文)を記述します。

また、キーが存在した時は何もしない(Updateしたくない)場合は、 when matched then ~を省略することで実現できます。

Oracle でUpsertが必要になった時は、是非この記事を参考にしてください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次