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.
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!