You are currently viewing [SOLVED] Teradata Failure 7547 Target row updated by multiple source rows
Photo by UX Indonesia on Unsplash
Could You Please Share This Post? I Appreciate It And Thank YOU! :) Have A Nice Day!
4.9
(1655)

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;
Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

BigData-ETL: image 7YOU MIGHT ALSO LIKE

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 1655

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?