1

I am trying to do a basic upsert on an iSeries db2 with the MERGE statement, similar to as described in Does DB2 have an "insert or update" statement? and http://db2performance.blogspot.com/2011/12/merge-make-your-upserts-quick.html. When executed, it gives me Row not found for MERGE. SQLSTATE=02000 instead of inserting the row. Since I have when not matched then insert in the statement, why will it return an error instead of inserting? I looked all over SO and didn't see this particular issue.

Here is the statement I'm using:

merge into UFDFTRN as T using (
    select * from UFDFTRN 
    where DFCNO = 354 and DFINV = 1179 and DFLC = 1 and DFDATE = '2017-01-31'
        and DFSPLT = 0 and DFSEQ = 100
) as S on (
    T.DFCNO = S.DFCNO and T.DFINV = S.DFINV and T.DFDATE = S.DFDATE and
    T.DFSPLT = S.DFSPLT and T.DFSEQ = S.DFSEQ
) when matched then 
    update set DFSEQ = 1000, DFTRAN = 0, DFITEM = 'F224', DFRITM = '0', 
        DFDESC = 'DAIRY VTM PREMIX', DFQTY = 3, DFUM = '',DESIQU = 0, DFRTQU = 3,
        DFUPR = 0, DFCTUP = 0, DFUCST = 0, DFOUCST = 0, DFAMT = 0, DFOAMT = 0, DFCODE = '',
        DFURAT = '', DFCGCD = '0', DFCTNO = 0, DFADJITM = '', DFADJPCT = 0, DFMNFITM = '',
        DFMNFRAT = '', DFMNFQTY = '0', DFMNFTQTY = '0'
when not matched then 
    insert (DFCNO, DFINV, DFLC, DFDATE, DFSPLT, DFSEQ, DFTRAN, DFITEM, DFRITM, DFDESC,
        DFQTY, DFUM, DFSIQU, DFRTQU, DFUPR, DFCTUP, DFUCST, DFOUCST, DFAMT, DFOAMT, DFCODE,
        DFURAT, DFCGCD, DFCTNO, DFADJITM, DFADJPCT, DFMNFITM, DFMNFRAT, DFMNFQTY, DFMNFTQTY
    ) values (
        354, 1179, 1, '2017-01-31', 0, 1000, 0, 'F224', '0', 'DAIRY VTM PREMIX', 3, '', 0,
        3, 0, 0, 0, 0, 0, 0, '', '', '0', 0, '', 0, '', '', '0', '0'
    )
Community
  • 1
  • 1
Kelly Keller-Heikkila
  • 2,544
  • 6
  • 23
  • 35
  • Your statement doesn't make any sense to me... The source file and target file are the same; that's not how you usually use merge. Additionally, I've never seen a merge with hardcoded values in the insert & update.. What are you trying to accomplish? – Charles Jan 31 '17 at 16:59
  • Sorry for the confusion. I have some data in a row that I want to update. If the row does not already exist, then I want a new row created. Basically, I want to do an upsert, similar to how one would do it in MySQL with `on duplicate key update`. The links I posted implied that upsert can be done using merge. Is there another/better way? I use the same table because I'm not really merging 2 different tables, but instead just trying to check to see if a record exists and if so, update it, and if not, insert it. – Kelly Keller-Heikkila Jan 31 '17 at 17:14
  • You're just trying to update 1 record, 1 time with a given set of values? Or a set of records with an unknown set of values? – Charles Jan 31 '17 at 18:40
  • Update 1 record 1 time with a given set of values. – Kelly Keller-Heikkila Jan 31 '17 at 19:42

3 Answers3

1

It probably should look more like this:

merge into UFDFTRN as T using (
    select 354 DFCNO, 1179 DFINV, 1 DFLC, '2017-01-31' DFDATE, 0 DFSPLT, 100 DFSEQ
           , 'DAIRY VTM PREMIX' f1 -- all other columns you might need
    from sysibm.sysdummy1 
) as S 
on (
    T.DFCNO = S.DFCNO and T.DFINV = S.DFINV and T.DFDATE = S.DFDATE and
    T.DFSPLT = S.DFSPLT and T.DFSEQ = S.DFSEQ
) 
when matched then 
    update set T.DFSEQ = S.DFSEQ, T.DFTRAN = S.DFTRAN, -- etc. etc.
when not matched then 
    insert (DFCNO, DFINV, ... -- etc. etc.
    ) values (
        S.DFSNO, S.DFINV, ..., S.F1, ...-- etc. etc.
    )

PS. Not tested.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
0

Mustaccio has the right format for the merge...

But as I commented, that's a really funny way to use merge.

Personally, for a 1 time thing, I would have just

update UFDFTRN 
set (DFCNO, DFINV, DFLC, DFDATE, DFSPLT, DFSEQ, DFTRAN, DFITEM, DFRITM, DFDESC,
        DFQTY, DFUM, DFSIQU, DFRTQU, DFUPR, DFCTUP, DFUCST, DFOUCST, DFAMT, DFOAMT, DFCODE,
        DFURAT, DFCGCD, DFCTNO, DFADJITM, DFADJPCT, DFMNFITM, DFMNFRAT, DFMNFQTY, DFMNFTQTY
    ) = (
        354, 1179, 1, '2017-01-31', 0, 1000, 0, 'F224', '0', 'DAIRY VTM PREMIX', 3, '', 0,
        3, 0, 0, 0, 0, 0, 0, '', '', '0', 0, '', 0, '', '', '0', '0'
    )
where DFCNO = 354 and DFINV = 1179 and DFLC = 1 and DFDATE = '2017-01-31'
        and DFSPLT = 0 and DFSEQ = 100

And if that failed with record not found, simply changed the update to an insert

insert into UFDFTRN 
 (DFCNO, DFINV, DFLC, DFDATE, DFSPLT, DFSEQ, DFTRAN, DFITEM, DFRITM, DFDESC,
        DFQTY, DFUM, DFSIQU, DFRTQU, DFUPR, DFCTUP, DFUCST, DFOUCST, DFAMT, DFOAMT, DFCODE,
        DFURAT, DFCGCD, DFCTNO, DFADJITM, DFADJPCT, DFMNFITM, DFMNFRAT, DFMNFQTY, DFMNFTQTY
    ) values (
        354, 1179, 1, '2017-01-31', 0, 1000, 0, 'F224', '0', 'DAIRY VTM PREMIX', 3, '', 0,
        3, 0, 0, 0, 0, 0, 0, '', '', '0', 0, '', 0, '', '', '0', '0'
    )
Charles
  • 21,637
  • 1
  • 20
  • 44
  • It's not really hardcoded in my code. I am only running the query once for each execution of the code, but the hardcoded values are actually variables in PHP, but the query I posted is what was actually sent to DB2. I wanted to do merge rather than 2 queries because I want to ensure there are not concurrency issues if the same query were executed by 2+ threads at the same time. – Kelly Keller-Heikkila Jan 31 '17 at 22:09
0

This use case is not unusual at all. This is the most basic use case for merge imo. You're trying to update or insert into 1 table. It really should be easier but the solution is to put any where clause in the on () portion of the statement and it starts working as expected. (worked db2 luw 10.5 at least.. I have the exact same situation) Merge apparently can't handle the using () portion's where clause. It only sees what's in on () portion for the update/insert criteria. Don't follow sysdummy1 answer as that isn't determining if the data actually exists in the target table it's just text. I don't want to write an insert and check failure because it will cause the app to throw an error and I really shouldn't have to trap the error and do that code if db2 has this feature.

adfasdf
  • 1