You will get this error code [ Failure 7547 Target row updated by multiple source rows ] when try update the target table using multiple records from source. It completely does not matter if you use MERGE or direct UPDATE statement. In this post I will show you where is the problem and how to fix it.
Problem in Failure 7547 Target row updated by multiple source rows
Let’s look at the below example:
UPDATE CUSTOMER C FROM ( SELECT CUST_ID, CUST_MAIL FROM ORDER ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
It look good, but only if we not have duplicates for CUST_ID attribute at ORDER table. It means error 7547 always occurs when you try update CUSTOMER table with multiple rows from ORDER table with the same CUST_ID. CUST_ID must be unique value at below query. Check if you have duplicates at your source table in your instance of Teradata!
Solution -> Teradata
#1 – Whichever Value
If it does not matter which rows from source table is appropriate, you can use DISTINCT statement at your query, e.g.:
UPDATE CUSTOMER C FROM ( SELECT DISTINCT CUST_ID, CUST_MAIL FROM ORDER ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
#2 – Current Record
However, in most of cases you can be interested in taken only a current version of the record for the specific CUST_ID attribute. It works specially if your source table can consists also a historical data. You need to looking for load data attribute at source table, e.g.:
UPDATE CUSTOMER C FROM ( SELECT CUST_ID, CUST_MAIL FROM ( SELECT CUST_ID, CUST_MAIL, RANK() OVER (PARTITION BY CUST_ID ORDER LOAD_DATE DESC) AS RANK_SRC FROM ORDER ) S WHERE S.RANK_SRC=1 ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!