Target Row Updated by Multiple Source Rows

teradata

While working on Teradata, you may have encounter a very common error while using UPDATE command:

Error 7547: Target row updated by multiple source rows

The reason why we get this error is due to duplicate rows in SOURCE table for given WHERE condition in UPDATE statement. For any update statement, in ideal condition, there should be only one row from SOURCE which should match to one or more rows in TARGET table. However if more than one row from SOURCE is matching to one record in TARGET then we will have this error.

In Update statement – one to many mapping from Source to Target is fine but many to one mapping from Source to Target will give this error.

Let us see by an example:

Data in TARGET Table:

SEL CUST_ID,CUST_NAME,CUST_CITY FROM CUSTOMERS;

CUST_ID CUST_NAME CUST_CITY
1 NITIN CHENNAI
2 RAJ BANGALORE
3 ANAND BANGALORE

Data in SOURCE Table:

SEL CUST_ID,CUST_CITY,DAT_MOD FROM TST_UPD;

CUST_ID CUST_CITY DAT_MOD
1 DELHI 2013-02-16
1 CHENNAI 2013-02-18

UPDATE COMMAND

UPDATE CUST
FROM CUSTOMERS CUST , TST_UPD TUPD
SET CUST_CITY= TUPD.CUST_CITY
WHERE
CUST.CUST_ID = TUPD.CUST_ID;

Now if I run an UPDATE for TARGET table with data from SOURCE table , I will get the error : TARGET ROW UPDATED BY MULTIPLE SOURCE ROWS; the reason is I have two rows in SOURCE with CUST_ID = 1. So while updating the TARGET table, TERADATA won’t be able to understand from which row it should update the TARGET row i.e. whether the CUST_CITY of TARGET table should be updated by DELHI or by CHENNAI. Hence the error.

How to overcome this Problem ?

You need to make sure that there is only one row in SOURCE table while updating the TARGET. There should not be many to one mapping from SOURCE to TARGET. So you can modify your UPDATE clause to make sure that only one row is there for each CUST_ID. If there are multiple entries then pick the CUST_ID which is latest i.e. most recent DAT_MOD. Below is the modified UPDATE clause which will execute perfectly fine.

UPDATE CUST
FROM CUSTOMERS CUST ,
(SEL CUST_ID,CUST_CITY,DAT_MOD FROM TST_UPD QUALIFY ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY DAT_MOD DESC)=1) TUPD
SET CUST_CITY= TUPD.CUST_CITY
WHERE
CUST.CUST_ID = TUPD.CUST_ID;

Now this update will work perfectly fine.

3 thoughts on “Target Row Updated by Multiple Source Rows”

Leave a Reply

Your email address will not be published. Required fields are marked *