• 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
    Precisely

    Participate in Precisely’s 2021 IBM i Security Survey

    Every year, Precisely asks IT pros responsible for IBM i security about their top challenges, strategies, technologies and best practices. These annual survey results provide a revealing look at the current state of IBM i security through the eyes of your peers.

    Click here to contribute your perspectives to this year’s survey.

    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

  • IBM Extends Dynamic Capacity Pricing Scheme To Its Cloud
  • Here’s What You Should Do About The IBM i Skills Shortage
  • Matillion Founder Recounts Midrange Roots
  • Four Hundred Monitor, February 24
  • IBM i PTF Guide, Volume 23, Number 8
  • iTech Solutions Keeps You In The Know With VERIFi
  • Tech Data’s Take On Certified Pre-Owned IT Gear
  • Guru: Prompting Stored Procedures
  • As I See It: Sunshine Or Oxygen?
  • Looking For Some Insight On IBM i Security

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.