DB2合并upsert获取“找不到行" [英] DB2 merge upsert gets 'Row not found for MERGE' error

问题描述

我正在尝试使用MERGE语句在iSeries db2上进行基本的upsert，类似于和 http://db2performance.blogspot.com/2011/12/merge-make-your-upserts-quick.html .执行后，它会给我 MERGE找不到行.SQLSTATE = 02000 而不是插入行.由于我在语句中有何时不匹配然后插入，为什么它会返回错误而不是插入?我四处张望，没有看到这个特殊的问题.

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'
    )

推荐答案

它可能看起来应该更像这样:

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.未经测试.

这篇关于DB2合并upsert获取“找不到行"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！