Error message when running Data Warehouse: "The MERGE statement attempted to UPDATE or DELETE the same row more than once." On DWH_LKP_TRANSLATION
Article ID: 125306
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaSClarity PPM On Premise
When running Load Data Warehouse it fails with error:
ERROR 2019-01-10 08:12:33,398 [Thread-4530072] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : Couldn't execute SQL: MERGE INTO DWH_LKP_TRANSLATION TGT USING ( select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, IS_ACTIVE as IS_ACTIVE, LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, SORT_ORDER as SORT_ORDER, NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, CONVERT(DATETIME ,'2019-01-10 08:12:32') as dw_updated_date from [PPMDBLINK].niku.niku.DWH_LOOKUPS_V where 1=1 AND LAST_UPDATED_DATE >= CONVERT(DATETIME,'2018-11-06 10:07:11') ) SRC ON ( SRC.TRANSLATION_KEY = TGT.TRANSLATION_KEY AND SRC.LANGUAGE_CODE = TGT.LANGUAGE_CODE ) WHEN MATCHED THEN UPDATE SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.IS_ACTIVE = SRC.IS_ACTIVE, TGT.LANGUAGE_CODE_KEY = SRC.LANGUAGE_CODE_KEY, TGT.SORT_ORDER = SRC.SORT_ORDER, TGT.TRANSLATION = SRC.TRANSLATION, TGT.dw_updated_date = SRC.dw_updated_date WHEN NOT MATCHED THEN INSERT (CLARITY_UPDATED_DATE, IS_ACTIVE, LANGUAGE_CODE, LANGUAGE_CODE_KEY, SORT_ORDER, TRANSLATION, TRANSLATION_KEY, dw_updated_date) VALUES (SRC.CLARITY_UPDATED_DATE, SRC.IS_ACTIVE, SRC.LANGUAGE_CODE, SRC.LANGUAGE_CODE_KEY, SRC.SORT_ORDER, SRC.TRANSLATION, SRC.TRANSLATION_KEY, SRC.dw_updated_date) ;
[CA Clarity][SQLServer JDBC Driver][SQLServer]The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Caused by unresolved duplicates in DWH_LKP_TRANSLATION on DWH side