• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Upsert One Row From A Data Structure

    January 26, 2016 Hey, Ted

    I’m attempting to use the SQL MERGE statement to write an “upsert” that stores the data to be inserted or updated in an externally described data structure. Is that possible? Any insight into this would be helpful.

    –Lewis

    Even though Lewis was only dealing with one row in the database table, what he was doing qualifies as an upsert in my book. He’s updating a row if it’s there and adding it if it isn’t. However, I don’t think MERGE is the right tool for this job.

    MERGE is designed to update a lot of rows in one table from a lot of rows in another one. If Lewis were trying to upsert a lot of rows from a data structure array or a multiple-occurrence data structure, maybe MERGE would make sense, but I’m pretty sure SQL won’t allow that.

    Instead, I recommended that he try either operation–INSERT or UPDATE–and if it fails, try the other one, like this:

    D CompanyInfo   e ds                  qualified extname(COMPANIES)
    
    D DUPLICATE_KEY   c                   const('23505')   
    
      exec sql  insert into companies values(:CompanyInfo);
    
      if SqlState = DUPLICATE_KEY
         exec sql   update companies
                       set row = :CompanyInfo
                     where ID  = :CompanyInfo.ID;
      endif;
    

    The COMPANIES table is keyed on a single column (field)–ID.

    What I really liked about Lewis’ code was the SET ROW expression that he used in his MERGE statement. I copied it into the UPDATE statement in my example. I wrote about SET ROW a few eons ago, but I don’t think I’ve ever mentioned using it with an RPG data structure. Using a data structure in the INSERT and UPDATE statements keeps them short and wieldy, and I do like wieldy code!

    RELATED STORIES

    The Powerful SQL Upsert

    Updating Two or More Fields Through a Join

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Townsend Security:  IBM Security QRadar, IBM i and Townsend Security - Better Together
    System i Developer:  RPG & DB2 Summit - March 22-24 in Dallas. Register by Feb 12 and save $300!
    Fresche Legacy:  Optimize IBM i apps; Improve business processes; Deliver modernization success

    Baseline Data Boss Predicts Steady Stream Of Outsourced Production Environments Ain’t Nobody’s Business But Your Own

    One thought on “Upsert One Row From A Data Structure”

    • Olivier Drubigny says:
      December 20, 2017 at 5:44 am

      Nethertheless you have to compile your source if the table companies is altered.

      Reply

    Leave a Reply Cancel reply

Volume 16, Number 02 -- January 26, 2016
THIS ISSUE SPONSORED BY:

Bug Busters Software Engineering
ProData Computer Services
WorksRight Software

Table of Contents

  • Run VisualAge RPG Applications On 64-bit Windows 7, 8, And 10 Over The LAN
  • Upsert One Row From A Data Structure
  • Parsing Delimited Text Data Natively in SQL, Part 1

Content archive

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

Recent Posts

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

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 © 2025 IT Jungle