Getting following error with my merge statement
Msg 8672, Level 16, State 1, Line 1 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.
what am i doing wrong in my statement
MERGE INTO DM.DATA_ERROR.DATA_ERROR_FACT_TABLE FT
USING DM.DATA_ERROR.DATA_ERROR_FACT_TABLE_Temp FTT
ON( FT.DATA_PROVIDER_SK = FTT.DATA_PROVIDER_SK
AND FT.SOURCE_SYSTEM_SK = FTT.SOURCE_SYSTEM_SK
AND FT.ERROR_SK = FT.ERROR_SK
AND FT.LOG_DATE_KEY = FTT.LOG_DATE_KEY
AND FT.PRIME_EDW_DATA_OBJECT_SK = FTT.PRIME_EDW_DATA_OBJECT_SK
AND FT.OTHER_EDW_DATA_OBJECT1_SK = FTT.OTHER_EDW_DATA_OBJECT1_SK
AND FT.OTHER_EDW_DATA_OBJECT2_SK = FTT.OTHER_EDW_DATA_OBJECT2_SK
AND FT.OTHER_EDW_DATA_OBJECT3_SK = FTT.OTHER_EDW_DATA_OBJECT3_SK
AND FT.OTHER_EDW_DATA_OBJECT4_SK = FTT.OTHER_EDW_DATA_OBJECT4_SK
AND FT.OTHER_EDW_DATA_OBJECT5_SK = FTT.OTHER_EDW_DATA_OBJECT5_SK
)WHEN
NOT MATCHED THEN INSERT ( [DATA_PROVIDER_SK]
,[ERROR_SK]
,[SOURCE_SYSTEM_SK]
,[CONTAINER_ID]
,[CONTAINER_START_DATE]
,[CONTAINER_END_DATE]
, [CONTAINER_SEQUENCE_NUMBER]
,[LATEST_CONTAINER_SEQUENCE_NUMBER_FLAG]
,[LOG_DATE_KEY]
,[LOG_DATE]
,[ERROR_RECORD_EFF_DATE_KEY]
,[RECORD_REJECTION_SITE]
,[RECORD_REJECTED_FLAG]
,[DATA_QUALITY_RATING]
,[DATA_ERROR_KEY]
,[PRIME_EDW_DATA_OBJECT_SK]
,[PRIME_EDW_TABLE]
,[PRIME_EDW_FIELD_NAME]
,[PRIME_EDW_FIELD_VALUE]
,[OTHER_EDW_DATA_OBJECT1_SK]
,[OTHER_EDW_TABLE1]
,[OTHER_EDW_FIELD_NAME1]
,[OTHER_EDW_FIELD_VALUE1]
,[OTHER_EDW_DATA_OBJECT2_SK]
,[OTHER_EDW_TABLE2]
,[OTHER_EDW_FIELD_NAME2]
,[OTHER_EDW_FIELD_VALUE2]
,[OTHER_EDW_DATA_OBJECT3_SK]
,[OTHER_EDW_TABLE3]
,[OTHER_EDW_FIELD_NAME3]
,[OTHER_EDW_FIELD_VALUE3]
,[OTHER_EDW_DATA_OBJECT4_SK]
,[OTHER_EDW_TABLE4]
,[OTHER_EDW_FIELD_NAME4]
,[OTHER_EDW_FIELD_VALUE4]
,[OTHER_EDW_DATA_OBJECT5_SK]
,[OTHER_EDW_TABLE5]
,[OTHER_EDW_FIELD_NAME5]
,[OTHER_EDW_FIELD_VALUE5]
,[CREATED_BY_AUDIT_SK]
,[UPDATED_BY_AUDIT_SK] )
VALUES
(
FTT.[DATA_PROVIDER_SK],
FTT.[ERROR_SK],
FTT.[SOURCE_SYSTEM_SK],
1,
FTT.[CONTAINER_START_DATE],
FTT.[CONTAINER_END_DATE],
FTT.[CONTAINER_SEQUENCE_NUMBER],
NULL,
FTT.[LOG_DATE_KEY],
FTT.[LOG_DATE],
FTT.[LOG_DATE_KEY], --NEED TO BE FIXED
FTT.[RECORD_REJECTION_SITE],
FTT.[RECORD_REJECTED_FLAG],
FTT.[DATA_QUALITY_RATING],
FTT.[DATA_ERROR_KEY],
FTT.[PRIME_EDW_DATA_OBJECT_SK],
FTT.[PRIME_EDW_TABLE],
FTT.[PRIME_EDW_FIELD_NAME],
FTT.[PRIME_EDW_FIELD_VALUE],
FTT.[OTHER_EDW_DATA_OBJECT1_SK],
FTT.[OTHER_EDW_TABLE1],
FTT.[OTHER_EDW_FIELD_NAME1],
FTT.[OTHER_EDW_FIELD_VALUE1],
FTT.[OTHER_EDW_DATA_OBJECT2_SK],
FTT.[OTHER_EDW_TABLE2],
FTT.[OTHER_EDW_FIELD_NAME2],
FTT.[OTHER_EDW_FIELD_VALUE2],
FTT.[OTHER_EDW_DATA_OBJECT3_SK],
FTT.[OTHER_EDW_TABLE3],
FTT.[OTHER_EDW_FIELD_NAME3],
FTT.[OTHER_EDW_FIELD_VALUE3],
FTT.[OTHER_EDW_DATA_OBJECT4_SK],
FTT.[OTHER_EDW_TABLE4],
FTT.[OTHER_EDW_FIELD_NAME4],
FTT.[OTHER_EDW_FIELD_VALUE4],
FTT.[OTHER_EDW_DATA_OBJECT5_SK],
FTT.[OTHER_EDW_TABLE5],
FTT.[OTHER_EDW_FIELD_NAME5],
FTT.[OTHER_EDW_FIELD_VALUE5],
54,
54
) WHEN MATCHED
AND ( FT.[CONTAINER_START_DATE] <> FTT.[CONTAINER_START_DATE]
OR FT.[CONTAINER_END_DATE] <> FTT.[CONTAINER_END_DATE]
OR FT.[LOG_DATE_KEY] <> FTT.[LOG_DATE_KEY]
OR FT.[LOG_DATE] <> FTT.[LOG_DATE]
OR FT.[RECORD_REJECTION_SITE] <> FTT.[RECORD_REJECTION_SITE]
OR FT.[RECORD_REJECTED_FLAG] <> FTT.[RECORD_REJECTED_FLAG]
OR FT.[DATA_QUALITY_RATING] <> FTT.[DATA_QUALITY_RATING]
OR FT.[DATA_ERROR_KEY] <> FTT.[DATA_ERROR_KEY]
OR FT.[PRIME_EDW_TABLE] <> FTT.[PRIME_EDW_TABLE]
OR FT.[PRIME_EDW_FIELD_NAME] <> FTT.[PRIME_EDW_FIELD_NAME]
OR FT.[PRIME_EDW_FIELD_VALUE] <> FTT.[PRIME_EDW_FIELD_VALUE]
OR FT.[OTHER_EDW_TABLE1] <> FTT.[OTHER_EDW_TABLE1]
OR FT.[OTHER_EDW_FIELD_NAME1] <> FTT.[OTHER_EDW_FIELD_NAME1]
OR FT.[OTHER_EDW_FIELD_VALUE1] <> FTT.[OTHER_EDW_FIELD_VALUE1]
OR FT.[OTHER_EDW_TABLE2] <> FTT.[OTHER_EDW_TABLE2]
OR FT.[OTHER_EDW_FIELD_NAME2] <> FTT.[OTHER_EDW_FIELD_NAME2]
OR FT.[OTHER_EDW_FIELD_VALUE2] <> FTT.[OTHER_EDW_FIELD_VALUE2]
OR FT.[OTHER_EDW_TABLE3] <> FTT.[OTHER_EDW_TABLE3]
OR FT.[OTHER_EDW_FIELD_NAME3] <> FTT.[OTHER_EDW_FIELD_NAME3]
OR FT.[OTHER_EDW_FIELD_VALUE3] <> FTT.[OTHER_EDW_FIELD_VALUE3]
OR FT.[OTHER_EDW_TABLE4] <> FTT.[OTHER_EDW_TABLE4]
OR FT.[OTHER_EDW_FIELD_NAME4] <> FTT.[OTHER_EDW_FIELD_NAME4]
OR FT.[OTHER_EDW_FIELD_VALUE4] <> FTT.[OTHER_EDW_FIELD_VALUE4]
OR FT.[OTHER_EDW_TABLE5] <> FTT.[OTHER_EDW_TABLE5]
OR FT.[OTHER_EDW_FIELD_NAME5] <> FTT.[OTHER_EDW_FIELD_NAME5]
OR FT.[OTHER_EDW_FIELD_VALUE5] <> FTT.[OTHER_EDW_FIELD_VALUE5]
) THEN UPDATE
SET FT.[CONTAINER_START_DATE] = FTT.[CONTAINER_START_DATE],
FT.[CONTAINER_END_DATE] = FTT.[CONTAINER_END_DATE],
FT.[LOG_DATE_KEY] = FTT.[LOG_DATE_KEY],
FT.[LOG_DATE] = FTT.[LOG_DATE],
FT.[RECORD_REJECTION_SITE] = FTT.[RECORD_REJECTION_SITE],
FT.[RECORD_REJECTED_FLAG] = FTT.[RECORD_REJECTED_FLAG],
FT.[DATA_QUALITY_RATING] = FTT.[DATA_QUALITY_RATING],
FT.[DATA_ERROR_KEY] = FTT.[DATA_ERROR_KEY],
FT.[PRIME_EDW_TABLE] = FTT.[PRIME_EDW_TABLE],
FT.[PRIME_EDW_FIELD_NAME] = FTT.[PRIME_EDW_FIELD_NAME],
FT.[PRIME_EDW_FIELD_VALUE] = FTT.[PRIME_EDW_FIELD_VALUE],
FT.[OTHER_EDW_TABLE1] = FTT.[OTHER_EDW_TABLE1],
FT.[OTHER_EDW_FIELD_NAME1] = FTT.[OTHER_EDW_FIELD_NAME1],
FT.[OTHER_EDW_FIELD_VALUE1] = FTT.[OTHER_EDW_FIELD_VALUE1],
FT.[OTHER_EDW_TABLE2] = FTT.[OTHER_EDW_TABLE2],
FT.[OTHER_EDW_FIELD_NAME2] = FTT.[OTHER_EDW_FIELD_NAME2],
FT.[OTHER_EDW_FIELD_VALUE2] = FTT.[OTHER_EDW_FIELD_VALUE2],
FT.[OTHER_EDW_TABLE3] = FTT.[OTHER_EDW_TABLE3],
FT.[OTHER_EDW_FIELD_NAME3] = FTT.[OTHER_EDW_FIELD_NAME3],
FT.[OTHER_EDW_FIELD_VALUE3] = FTT.[OTHER_EDW_FIELD_VALUE3],
FT.[OTHER_EDW_TABLE4] = FTT.[OTHER_EDW_TABLE4],
FT.[OTHER_EDW_FIELD_NAME4] = FTT.[OTHER_EDW_FIELD_NAME4],
FT.[OTHER_EDW_FIELD_VALUE4] = FTT.[OTHER_EDW_FIELD_VALUE4],
FT.[OTHER_EDW_TABLE5] = FTT.[OTHER_EDW_TABLE5],
FT.[OTHER_EDW_FIELD_NAME5] = FTT.[OTHER_EDW_FIELD_NAME5],
FT.[OTHER_EDW_FIELD_VALUE5] = FTT.[OTHER_EDW_FIELD_VALUE5],
FT.[UPDATED_BY_AUDIT_SK] = 54 ;