Teradata Failure 7547 Target row updated by multiple source rows

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!