Upsert One Row From A Data Structure

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