• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • The Powerful SQL Upsert

    January 27, 2015 Ted Holt

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    WEBINAR
    Unit Test Automation: Secure Application Quality on IBM i
    April 15, 12 p.m. EDT, 5 p.m. BST

    Unit testing is arguably the most effective element of your testing strategy, driving the quality of your application as it is being developed.  Yet how can unit testing benefit legacy applications on IBM i – and especially those containing ‘monolithic’ sections of code?

    In this Webinar we will demonstrate how specialized unit test automation can safeguard application quality and generate reusable test assets for both modular and monolithic code.

    You will learn how to automate the IBM i unit testing process within a standard DevOps stack., including RDi, JUnit and Jenkins.

    Using ARCAD iUnit, you’ll see:

    • Automated test creation for modules, programs and service programs
    • Code coverage results
    • Mock capability – simulates key components like data, files programs
    • Test execution history
    • Version control with Git

    Register today and secure the quality of your IBM i application.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Reach Your Modernization Goals. Register for the February 25 Webinar now!
    New Generation Software:  Ask us about Query, Reporting, and Analytics. Order a FREE Trial of NGS-IQ.
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 17-19

    Reader Feedback On IBM i Wish List For 2015 DB2 Enhancements, Free Form RPG, Modernization Top Rowe’s ‘Big Hits’ List

    Leave a Reply Cancel reply

Volume 15, Number 02 -- January 27, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
System i Developer

Table of Contents

  • Everybody Likes Shortcuts! Part 2, Playing With Blocks
  • The Powerful SQL Upsert
  • Knee-Deep In Ruby Waters

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15
  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.