I have written a MERGE statement as below
MERGE INTO ndeshpande.attraction_pref_prod_bkup AS Prod
(SELECT Patron_id, Attraction_Id, Pgm_Cd, Affinity_Src_Cd, Start_Ts,End_Ts, Cur_Ind, Patron_Lkup_Id
, Favorite_Flg, Opt_In_Flg, ECDB_Affinity_src_id, Last_Batch_ID, Init_Run_ID, Last_Run_ID
, Opt_In_Camefrom_Cd, CREATE_DTTM FROM ndeshpande.attraction_pref) AS Ref
ON Prod.Patron_id = Ref.Patron_id AND prod.Attraction_Id = ref.Attraction_Id
AND prod.Pgm_Cd = ref.Pgm_Cd AND prod.Affinity_Src_Cd = Ref.Affinity_Src_Cd-- AND prod.ECDB_Affinity_src_id = Ref.ECDB_Affinity_src_id
WHEN MATCHED THEN UPDATE
Pgm_Cd = Ref.Pgm_Cd
, Affinity_Src_Cd = 'LN'
, Start_Ts = Ref.Start_Ts
, Cur_Ind = 'Y'
, Favorite_Flg = NULL
, Opt_In_Flg = NULL
, ECDB_Affinity_src_id = 66
, Last_Batch_ID = NULL
, Init_Run_ID = NULL
, Last_Run_ID = NULL
, Opt_In_Camefrom_Cd = NULL
, CREATE_DTTM = CAST(CURRENT_DATE AS TIMESTAMP(0))
WHEN NOT MATCHED THEN INSERT
Ref.Patron_ID , Ref.Attraction_Id, Ref.Pgm_Cd, Ref.Affinity_Src_Cd, Ref.Start_Ts, Ref.End_Ts, Ref.Cur_Ind, Ref.Patron_Lkup_Id
, Ref.Favorite_Flg, Ref.Opt_In_Flg, Ref.ECDB_Affinity_src_id, Ref.Last_Batch_ID, Ref.Init_Run_ID, Ref.Last_Run_ID
, Ref.Opt_In_Camefrom_Cd, Ref.CREATE_DTTM
When I try to run it the first time on a test table, it works fine and inserts the rows I want. However, when I execute the statement again, it fails with below error:
MERGE Failed.  Target row updated by multiple source rows.
My questions is, isn't the second execution supposed to update the table? Why is this error coming? If I am running merge on the same source table the second time, should it not just update the target table (although it is updating same rows with same content)?
If your first time run had duplicate rows (at least on keys), this problem would occur. During the first time run the duplicate rows are inserted without any issues, while running for the second time the keys matched and it goes into update portion where it becomes ambiguous for teradata to choose which one as source to update the target row. So it fails with the error target row updated by multiple source rows.
Insert is done without a condition unless a USI or UPI or SET table is defined so duplicates are allowed here, But Update portion is done to specific matching condition given on the SQL which eventually leads to ambiguity in choosing the source row. Needless to mention PI should be a part of this condtion for merge to work.
Thank you Kirthi.
Just to mention in the query above, target table ndeshpande.attraction_pref_prod_bkup and source table ndeshpande.attraction_pref have PRIMARY INDEX ( Patron_ID ) and INDEX ( Attraction_Id ) in their definition. However, I do not think they are unique indexes. Is there any way to avoid the duplicates or adding more columns to the index composition is the only answer here?
I am ready to use something other than Merge also.
You need to use the logical Primary Key of your target table for matching, i.e. the target row must be unique.
Regarding the error during MATCHED, you might have multiple rows for the same PK, in that case you must add some logic to the SELECT, e.g. SUM + GROUP BY...
To choose 1 row between the duplicates should be a business call which ultimately defines the unique key of the record, with which you can create unique secondary index or re-create the table with UPI.
Alternatively you can use qualify statement too in your select clause, if you prefer to not touch the Table structures.
Basically its not the problem with the Merge, its the decsion which tells which source row to update the target rows when we have 2 or more source rows to update the target row(s).
Thanks a ton guys........ I tried max+group by technique and it worked. Below is the modified section:
SELECT Patron_id, Attraction_Id, MAX(Pgm_Cd)AS Pgm_Cd,
MAX(Affinity_Src_Cd) AS Affinity_Src_Cd, MAX(Start_Ts)AS Start_Ts
,MAX(End_Ts) AS End_Ts, MAX(Cur_Ind) AS Cur_Ind, MAX(Patron_Lkup_Id) AS Patron_Lkup_Id
, MAX(Favorite_Flg) AS Favorite_Flg, MAX(Opt_In_Flg) AS Opt_In_Flg
, MAX(ECDB_Affinity_src_id)AS ECDB_Affinity_src_id , MAX(Last_Batch_ID) AS Last_Batch_ID
, MAX( Init_Run_ID) AS Init_Run_ID, MAX(Last_Run_ID) AS Last_Run_ID
, MAX(Opt_In_Camefrom_Cd) AS Opt_In_Camefrom_Cd, MAX(CREATE_DTTM )AS CREATE_DTTM
FROM ndeshpande.attraction_pref GROUP BY Patron_id, Attraction_Id
) AS Ref