「【総まとめ】UPSERTとは？DBごとの違いは？」では、主要なDBごとの書き方をざっくり説明しました。
今回は、SQL Server（及びSQL Database）でのUpsertに焦点を絞り、深堀していきたいと思います。
参考までに、DataFrameからUpsertを生成するPython関数も紹介していますので、必要に応じてコピぺしてご利用下さい。
SQL Server は MERGE ～ で Upsertを行う
SQL Server で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 datetime 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を丸ごとコピー） select * into old_oders 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 |
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 (shop_no,order_date,product_id,count); |
1行をUpsertする
任意の値をUpsertしたい場合は、USING テーブル名 の代わりに、(select 列名1 AS 値1,・・・）と記述します。「１行だけの一時的なテーブルを作って、それを USINGに指定する」と考えると分かり易いでしょう。
下記は、サンプルテーブルに (1,'2023/01/01 09:00:00',10000,5) のデータをインサートし、レコードが既に存在していれば 数量(count)を 5に更新するサンプルです。
|
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) 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 = 5 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,10000 product_id,5 count) 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 (shop_no,order_date,product_id,count); |
複数行をまとめてUpsertする
SQL Serverでは、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/01/01 09:10:00' order_date,10000 product_id,15 count union select 1 shop_no,'2023/01/01 09:20:00' order_date,10000 product_id,110 count union select 1 shop_no,'2023/01/01 09:30:00' order_date,10000 product_id,115 count ) 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 (shop_no,order_date,product_id,count); |
尚、下図のように1回のUpsertの中で キーが重複するデータをInsertした場合、「PRIMARY KEY違反 〇〇〇は重複するキーを挿入できません」又は「同じ行に対してUPDATE又はDELETEが実行されました」というエラーになります。
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) 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 (shop_no,order_date,product_id,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 (shop_no,order_date,product_id,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))] # merge句の作成 values = ','.join(items).replace("'nan'", "NULL") merge = f'merge into {table} t1 using (select {values}) 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(df.columns) unmatched = f"when not matched then insert values({column_list}) " # insert、conflict、updateを結合 sqls.append(merge + condition + matched + unmatched) return sqls |
まとめ
今回はSQLServerのUpsertに焦点を絞って詳細を解説しました。
SQLServerでUpsertを行うには、Merge into ～ に更新先テーブル名、using ～に更新元テーブル名、on ～ に結合条件を記載します。そして、 when not matched then ～ にキーが存在しなかった処理（通常は insert文）、when matched then ～ にキーが存在した時の処理（通常はUpdate文）を記述します。
また、キーが存在した場合、Updateしたくないなら when matched then ～を省略することで実現できます。
SQLServerでUpsertが必要になった時は、是非この記事を参考にしてください。
コメント