Teradata – ambiguous table & multiple source rows update error

Table/view/trigger name is ambiguous – Not the obvious reason.

In some cases, we write database name explicitly in our queries however most of the time we rely on default database to select the view/table used in queries. However do you know in Teradata , you can have more than one default database for your query.

Let’s look at a query below :

DATABASE DB_A;

SELECT a.col1 FROM table_a a
INNER JOIN DB_B.table_b b
ON a.col1 = b.col1
INNER JOIN table_c c
ON b.col1 = c.col1;

The above query will fail with error:

E(3806):Table/view/trigger name ‘table_a’ is ambiguous.

This is because table_a exists in DB_A as well as in DB_B. So what exactly is causing this error ?
Before the query starts, default database is set to DB_A.
Now in the query when we explicitly mention DB_B, it is also added in default database list. So now TD will search for db objects used in query in both the databases i.e. search for table/views in DB_A and in DB_B.
Since table_a exists @ both places, hence the error.

Possible solution:

a) Either write database name for all the tables/views explicitly if it resides in both databases.
b) Create the one which is in other database as derived table:

SELECT a.col1 FROM table_a a
INNER JOIN (SEL * FROM DB_B.table_b) b
ON a.col1 = b.col1
INNER JOIN table_c c
ON b.col1 = c.col1;

Target row updated by multiple source rows.

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.