「【総まとめ】UPSERTとは？DBごとの違いは？」では、主要なDBごとの書き方をざっくり説明しました。
今回は、Oracle でのUpsertに焦点を絞り、掘り下げていきたいと思います。
参考として、DataFrameからUpsertを生成するPython関数も紹介しています。必要に応じてコピぺしてご利用下さい。
Oracle は MERGE ～ で Upsertを行う
Oracle でUpsertを行う場合は、MERGE INTO ～ WHEN ～を使います。
MERGE INTO～に更新先（インサート先）のテーブル名、 USING ～に更新元のテーブル名、ON ～ にユニークキーを指定します。
そして、WHEN MATCHED THEN ～には キーが存在した場合の処理（通常はUpdate文）、WHEN NOT MATCHED THEN ～ には キーが存在しなかった場合の処理（通常は Insert文）を記述します。
任意の値を Upsert したい場合は、 USINGに続けて (select 値1,値2,・・・）と記述します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- table_B から table_AにUpsertする場合 merge into table_A t1 using table_B t2 on (t1.shop = t2.shop and t1.name=t2.name) when matched then update set t1.age = t2.age when not matched then insert values (shop,name,age); -- table_A に1行登録する場合 merge into table_A t1 using (select 1 as shop,'yamada' as name,15 as age) t2 on (t1.shop = t2.shop and t1.name=t2.name) when matched then update set t1.age = t2.age when not matched then insert values (shop,name,age); |
検証データ
本記事のUpsert文を実際に試す場合は、あらかじめ以下のSQLを実行して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--サンプルテーブルの作成とテストデータ登録 CREATE TABLE oders ( shop_no integer not null, order_date timestamp not null, product_id integer not null, count integer not null, constraint oders_p_key primary key (shop_no,order_date,product_id) ); insert into oders values(1,'2023/01/01 09:00:00',10000,5); insert into oders values(1,'2023/01/01 09:10:00',10000,15); insert into oders values(1,'2023/01/01 09:20:00',10000,25); --下記はテーブル間のUpsert確認用(odersを丸ごとコピー） create table old_oders as select * from oders |
テーブル間でUpsertする
テーブル間でUpsertする場合は次のように記述します。
テーブルAとテーブルBは同じ列名が存在するため、各列名の先頭にエイリアス名（又はテーブル名）を付けて、どちらのテーブルに所属するものかを明示する必要があります。
ただし、insert (列名1,列名2,・・・）の列名については、エイリアス名を省略できます。
|
1 2 3 4 5 6 7 8 |
merge into oders t1 using old_oders t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when matched then update set t1.count = t2.count when not matched then insert(shop_no,order_date,product_id,count) values (t2.shop_no,t2.order_date,t2.product_id,t2.count); |
また、insert （列名１，列名２,・・・）の列名列挙部分は省略することが可能です。
下記は 省略した例になります。
|
1 2 3 4 5 6 7 8 |
merge into oders t1 using old_oders t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when matched then update set t1.count = t2.count when not matched then insert values (t2.shop_no,t2.order_date,t2.product_id,t2.count); |
1行をUpsertする
任意の値をUpsertしたい場合は、USING テーブル名 の代わりに、(select 列名1 AS 値1,・・・）と記述します。「１行だけの一時的なテーブルを作って、それを USINGに指定する」と考えると分かり易いでしょう。
下記は、サンプルテーブルに (1,'2023/01/01 09:00:00',10000,5) のデータをInsertし、レコードが既に存在していれば 数量(count)を 5にUpdateするサンプルです。
|
1 2 3 4 5 |
merge into oders t1 using (select 1 shop_no,'2023/01/01 09:00:00' order_date,10000 product_id,5 count from dual) t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when matched then update set t1.count = 15 when not matched then insert values (1,'2023/01/01 09:00:00',10000,5); |
ON～に記述する結合条件が真なら MATCHED THEN ～を、偽なら NOT MATCHED THEN ～を実行するので、テーブルBとは全く異なる値でUpdateやInsertを行うことも可能です。
例えば、キーが存在しなければ Insert 時に count を 5で登録し、キーが存在すれば count を 15で Updateするという事もできます。
通常は、テーブルAとテーブルBを同じ内容にするためのUpsertですから、直接の値を記述するのではなく、列名を使って更新したい値を指定します。
もうお気づきかもしれませんが、このMERGE文は、USING 部分を除いて、テーブルBからテーブルAへUpsertする書き方と全く同じです。
下記は簡略化した時のサンプルですが、 USING (SELECT ～）の AS は省略しています。
|
1 2 3 4 5 |
merge into oders t1 using (select 1 shop_no,'2023/01/01 09:00:00' order_date,20000 product_id,5 count from dual) t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when matched then update set t1.count = t2.count when not matched then insert values (t2.shop_no,t2.order_date,t2.product_id,t2.count); |
複数行をまとめてUpsertする
Oracleでは、1回のInsert文で複数行（複数レコード）をまとめて登録する マルチインサート文が使えますが、Upsertについても同様のことが行えます。
ポイントは、using の部分を union でつなげるところです。こうすることによって、複数行を持つテーブルが一時的に出来たように見え、テーブル間のUpsertが実行できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
merge into oders t1 using (select 1 shop_no,'2023/02/01 09:10:00' order_date,20000 product_id,5 count from dual union select 1 shop_no,'2023/02/01 09:20:00' order_date,20000 product_id,5 count from dual union select 1 shop_no,'2023/02/01 09:30:00' order_date,20000 product_id,15 count from dual ) t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when matched then update set t1.count = t2.count when not matched then insert values (t2.shop_no,t2.order_date,t2.product_id,t2.count) |
尚、次のように1回のUpsertの中で、キーが重複するデータが存在しても、順番に処理されるだけでエラーにはなりません。この場合は後勝ちになるので、最後に実行されたInsert文の値（下記の例では15）が最終的に書き込まれ(Updateされ）ます。
UpsertでInsertだけ行う（Updateしない）
WHEN MATCHED THEN UPDATE SET ～ の記述をしなければ、Updateされません。
|
1 2 3 4 5 6 7 8 9 10 11 |
-- oders に向けてインサートを行うが、既に存在する場合は何もしない merge into oders t1 using (select 1 shop_no,'2023/01/01 09:00:00' order_date,10000 product_id,115 count from dual) t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when not matched then insert values (t2.shop_no,t2.order_date,t2.product_id,t2.count); -- old_odersから oders に向けてインサートを行うが、既に存在するレコードには何もしない merge into oders t1 using old_oders t2 on (t1.shop_no = t2.shop_no and t1.order_date=t2.order_date and t1.product_id=t2.product_id) when not matched then insert values (t2.shop_no,t2.order_date,t2.product_id,t2.count); |
Pythonのサンプル
それでは、参考例としてDataFrameの値からUpsert文を生成するPythonのサンプルプログラムについてご紹介します。
使い方のサンプル
create_upsert にDataFrame、テーブル名、ユニークキーのリストを指定するだけで、Merge文を生成してくれます。
尚、下記サンプルで生成したUpsert文は、冒頭に紹介したサンプルテーブルに対して実行が可能です。
|
1 2 3 4 5 6 7 8 9 10 |
datas = [ [1,'2023/01/01 09:00:00',10000,55], [1,'2023/01/01 09:10:00',10000,65], [1,'2023/01/01 09:20:00',10000,75] ] df = pd.DataFrame(datas,columns=['shop_no','order_date','product_id','count']) sqls = create_upsert(df,'oders',['shop_no','order_date','product_id']) for sql in sqls: print(sql) |
関数のソースコード
以下は、関数本体のソースコードになります。数値以外のデータ（文字列、日付）はシングルクォートで括る必要があるので、簡易的に dtype が 'object' か否かで判断しています。
また、insert into ～ values ～ の values に渡す値に 'nan' が含まれている場合、NULL に置換する処理を入れています。もし更新する値の中に 'nan' という文字列が含まれると、NULLに変換されてしまう点にご注意下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
def create_upsert(df,table,key_columns): ''' DataFrameの内容からUpser文を作成する Parameters ---------- df : DataFrame データを格納したDataFrame table : str Upsert対象のテーブル名 key_columns : list of str ユニークキーのカラム 例:['shop_no','order_date',・・・]" Returns ---------- res:str 生成されたマルチテーブルインサート文 ''' # df から全てのカラム名を取り出す columns = ",".join([i for i in df.columns]) # シングルクォート格納用のリストを作り、カラムの型がobjectの場合のみシングルクォートをセット quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes] sqls = [] # 全ての行を取り出す for row in df.itertuples(): # 0カラム名（行インデックス）を飛ばし、必要に応じて各カラム値をシングルクォートで括る。 items = [ f'{quote[i]}{str(row[i+1])}{quote[i]} AS {df.columns[i]}' for i in range(len(df.columns))] # Insert句の作成 values = ','.join(items).replace("'nan'", "NULL") merge = f'merge into {table} t1 using (select {values} from dual) t2 ' # on句の作成 keys = ' and '.join([ f't1.{x}=t2.{x}' for x in key_columns]) condition = f'on ({keys}) ' # matched句の作成 set_list = ','.join([f't1.{x}=t2.{x}' for x in df.columns if x not in key_columns]) matched = f"when matched then update set {set_list} " # unmatched句の作成 column_list = ','.join([f't2.{x}' for x in df.columns]) unmatched = f"when not matched then insert values({column_list}) " # insert、conflict、updateを結合 sqls.append(merge + condition + matched + unmatched) return sqls |
まとめ
今回は Oracle のUpsertに焦点を絞って詳細を解説しました。
Oracle でUpsertを行うには、Merge into ～ に更新先テーブル名、using ～に更新元テーブル名、on ～ に結合条件を記載します。そして、 when not matched then ～ にキーが存在しなかった処理（通常は insert文）、when matched then ～ にキーが存在した時の処理（通常はUpdate文）を記述します。
また、キーが存在した時は何もしない（Updateしたくない）場合は、 when matched then ～を省略することで実現できます。
Oracle でUpsertが必要になった時は、是非この記事を参考にしてください。
コメント