0

When I run the following:

merge into LIBRARYA.TABLEA mergeTarget
using LIBRARYB.TABLEB mergeUsing
on 
    mergeTarget.COLA1 = mergeUsing.COLB1
    and mergeTarget.COLA2 = mergeUsing.COLB2
when matched then update set mergeTarget.COLA3 = mergeUsing.COLB3;

It gives the following error:

SQL State: 21506

Vendor Code: -788

Message: [SQL0788] Duplicate row identified for MERGE target TABLEA in LIBRARYA. Cause . . . . . : The set of rows identified in the USING clause of the MERGE statement identifies the same row more than once in target table or view TABLEA in LIBRARYA specified in the INTO clause. Any row in the target table or view may only be identified once by the input of the USING clause. Recovery . . . : Change the data identified in the USING clause such that no rows occur more than once in the target table or view. Try the request again.

However, the following query returns no results:

select COLA1, COLA2
from LIBRARYA.TABLEA
inner join LIBRARYB.TABLEB on COLA1 = COLB1 and COLA2 = COLB2
GROUP BY COLA1, COLA2
HAVING COUNT(*) > 1

Am I missing something, here? Shouldn't that second query detect any duplicates for the merge in the first query?

  • try to add COLA1 is not null and COLA2 is not null in your merge statement and see how it goes. – jyao Oct 11 '17 at 18:34
  • @jyao No change. – Sarov Oct 11 '17 at 19:12
  • I guess your verification query should be written in such way. Just try it and see whether you get anything select COLB1, COLB2 from LIBRARYA.TABLEA inner join LIBRARYB.TABLEB on COLA1 = COLB1 and COLA2 = COLB2 GROUP BY COLB1, COLB2 HAVING COUNT(*) > 1 – jyao Oct 12 '17 at 0:01
  • @jyao Still gives no results. – Sarov Oct 12 '17 at 13:09
  • There's probably something in your actual statement that causes this, which you redacted. – mustaccio Oct 12 '17 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.