2015.03.25
Ted Holt 著

強力な SQL Upsert(アップサート)

ご存知のとおり、ビジネス・コンピューティングで一般的な作業として、存在するエンティティー (顧客、ベンダー、発注書明細行) を更新し、存在しない場合は追加する作業があります。RPG では、条件ステートメントで update と write という 2 つの命令が必要です。SQL では 1 つのステートメントでシバン全体を処理します。では、それがどのように動作するのか見てみましょう。

こうした状況を図示した RPG III コードを下に示します。

FCUSTF UF E K DISK
. . . コード省略
C CUSKEY KLIST
C KFLD COMP
C KFLD ACCT
C*
. . . さらにコード省略
C* HILOEQ
C CUSKEY CHAINCUSTREC 99
C*
. . . フィールドに読み込むコード省略
C*
C *IN99 IFEQ *OFF
C UPDATCUSTREC
C ELSE
C WRITECUSTREC
C ENDIF
. . . 他

プログラムはカスタマー・ファイルにランダム読み取り (CHAIN) を行います。読み取りが成功すると (顧客がデータベースにいるなど)、プログラムは標識 99 をオフにします。読み取りが失敗すると、標識 99 がオンになります。

いったんフィールドが変更されたら、データをデータベースに格納します。プログラムは標識 99 を使用して、データの更新または追加を制御します。

SQL の世界では、こうしたタイプの出力命令は非公式に "update" と "insert" を組み合わせて "upsert" と呼ばれています。upsert を処理する SQL ステートメントは MERGE です。

RPG III の例で行ったようなことを行う SQL のプログラム片を以下に示します。

D aCompany s 3p 0
D aAccount s 5p 0
D aName s 20a
D aCity s 15a
D aState s 2a
D aZip s 10a

exec sql
   merge into custf as tgt
   using (values(:aCompany, :aAccount, :aName,
         :aCity, :aState, :aZip))
      as src (Company, Account, Name, City, State, Zip)
     on (tgt.Comp, tgt.Acct) = (src.Company, src.Account)
   when matched then(マッチしたら次に)
     update set tgt.Comp = src.Company,
         tgt.Acct = src.Account,
         tgt.Name = src.Name,
         tgt.City = src.City,
         tgt.State = src.State,
         tgt.Zip = src.Zip
   when not matched then(マッチしなかったら次に)
     insert values(src.Company, src.Account, src.Name,
            src.City, src.State, src.Zip);

ホスト変数 aCompany、aAccount、aName、aCity、aState、aZip に適切な値が読み込まれたら、データをデータベースに格納します。MERGE を一つずつ分解すると以下のようになります。

merge into custf as tgt

更新するデータベース表は CUSTF で、ここでは相関名 tgt (target) が指定されています。

using (values(:aCompany, :aAccount, :aName,
     :aCity, :aState, :aZip))
   as src (Company, Account, Name, City, State, Zip)

データベースにマージするデータは、前述の 6 つのホスト変数です。values 関数は相関名 src (source) として変数を 1 行の派生表にグループ分けしています。この派生表には Company、Account、Name、City、State、Zip という 6 つの列があります。

on (tgt.Comp, tgt.Acct) = (src.Company, src.Account)

CUSTF および値により作成された派生表は、会社およびアカウント番号と照合されます。

when matched then(マッチしたら次に)
   update set tgt.Comp = src.Company,
     tgt.Acct = src.Account,
     tgt.Name = src.Name,
     tgt.City = src.City,
     tgt.State = src.State,
     tgt.Zip = src.Zip

会社およびアカウントの行 (レコード) がすでに CUSTF にある場合、ホスト変数から作成された派生表のデータで行を更新します。

when not matched then(マッチしなかったら次に)
   insert values(src.Company, src.Account, src.Name,
     src.City, src.State, src.Zip);

指定された会社およびアカウントの行が存在しない場合は、新しい行を CUSTF 表に追加します。

少し練習すれば、誰にも負けないぐらい upsert できることでしょう。

ページトップ

ボタン