Error message when running Data Warehouse: "The MERGE statement attempted to UPDATE or DELETE the same row more than once." On DWH_LKP_TRANSLATION

Document ID : KB000125306
Last Modified Date : 25/01/2019
Show Technical Document Details
Issue:
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.
 
 
Cause:
Caused by unresolved duplicates in DWH_LKP_TRANSLATION on DWH side
Resolution:
1. On ppm_dwh database please run the following:
truncate table DWH_LKP_TRANSLATION
2. Run a Full Load of Data Warehouse