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?