The Powerful SQL Upsert

As you well know, a common task in business computing is to update an entity (customer, vendor, purchase order line, etc.) that exists, but add the entity if it doesn’t exist. In RPG this requires two operations–an update and a write–within a conditional statement. In SQL one statement handles the whole shebang. Here’s how it works.

Here’s some RPG III code that illustrates the situation.

FCUSTF   UF  E           K        DISK
 . . .  code omitted
C           CUSKEY    KLIST
C                     KFLD           COMP
C                     KFLD           ACCT
C*
 . . .  more code omitted
C*                                              HILOEQ
C           CUSKEY    CHAINCUSTREC              99
C*
 . . . code to load the fields omitted
C*
C           *IN99     IFEQ *OFF
C                     UPDATCUSTREC
C                     ELSE
C                     WRITECUSTREC
C                     ENDIF
 . . . etc.

The program does a random read (CHAIN) to the customer file. If the read is successful (i.e., the customer is in the database), the program turns off indicator 99. If the read fails, indicator 99 comes on.

Once the fields have been changed, it’s time to store the data in the database. The program uses indicator 99 to control whether to update or add the data.

In the world of SQL, this type of output operation is informally called an “upsert”, a combination of “update” and “insert”. The SQL statement that handles upserts is MERGE.

Here’s a program fragment with SQL that does the same sort of thing the RPG III example does.

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);

Host variables aCompany, aAccount, aName, aCity, aState, and aZip have been loaded with the appropriate values and it’s time to store the data in the database. Here’s a breakdown of the MERGE, piece by piece.

merge into custf as tgt

The database table to be updated is CUSTF, here given a correlation name of tgt (target).

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

The data to be merged into the database is in the six aforementioned host variables. The values function groups them into a derived table of one row, known by the correlation name src (source). This derived table has six columns, named Company, Account, Name, City, State, and Zip.

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

The CUSTF and the derived table created by values are to be matched on company and account number.

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

If a row (record) for the company and account is already in CUSTF, update the row with the data from derived table created from the host variables.

when not matched then
   insert values(src.Company, src.Account, src.Name,
                 src.City, src.State, src.Zip); 

If no row exists for the specified company and account, add a new row to the CUSTF table.

With a little practice, you’ll soon be upserting with the best of them!

RELATED STORIES

Merge Into the Synchronization Fast Lane with DB2 for i 7.1

Updating Through A Join With SQL, Take Three