• 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
    Fresche Solutions

    ON-DEMAND SESSION

    Protecting Your IBM i Systems from Ransomware and Other Cyber Threats

    Zero-day attacks and ransomware threats are on the rise and data that resides on IBM i is not immune. Now is the time to learn how to defend it.

    Join Marcel Sarrasin, CPO, Fresche and Pauline Brazil Ayala, VP of Operations, Trinity Guard as they introduce you to TGSuite, the next generation of IBM i security tools and dive into IFS and network security to help you learn how to configure the defenses on your system and guard your valuable data.

    In the session, Pauline and Marcel will discuss:

    • What a secure system looks like in 2022
    • Cybersecurity and auditing, data-level reporting and job activity monitoring
    • Advanced exit point security – knowing and managing who has access to your IBM i
    • Setting up alerts on critical security events as they happen
    • Managing all your LPARs from one centralized web console

    Watch Now!

    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

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

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 © 2022 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.