Teradata: Failure 7547 Target row updated by multiple source rows

You will get this error code 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

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 occures when you try update CUTOMER 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!

Solution

#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 hisorical 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 or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

Leave a Reply

avatar
  Subscribe  
Notify of