Update subquery problem 1

[WILLIEWANKA]

Hello all, i'm trying to update a table using a case statement from another table.

Table A (the table being updated) contains four columns - act_nbr, tran_date, tran_amount, and cycle_month. This is a SET table, so there are no duplicate rows. The primary key is act_nbr, tran_date, tran_amount.

Table B contains two attributes - act_nbr, statement_cycle (the day the statement was generated).

I am trying to update table A with the month the matching statement was generated (a created value from a subquery that joins table A and table B). The subquery is joining to the updated table on the three primary key columns from table A but I keep getting

Error 7547 : Target Row updated by multiple source rows.


I have checked for duplicate rows using the three join attributes, but there are no duplicate rows.

Here is the statement

UPDATE TABLE_A FROM
TABLE_A, (SELECT DISTINCT A.ACT_NBR,
A.TRAN_DATE,
A.TRAN_AMOUNT,
CASE
WHEN B.STATEMENT_CYCLE > EXTRACT (DAY FROM A.TRAN_DATE) THEN EXTRACT (MONTH FROM A.TRAN_DATE)
ELSE EXTRACT (MONTH FROM A.TRAN_DATE) + 1 END "P_MONTH" FROM TABLE_A A INNER JOIN SUBQUERY_TABLE_B B ON A.ACT_NBR=B.ACT_NBR) TABLE_B
SET
A.CYCLE_MONTH = B.P_MONTH
WHERE
A.ACT_NBR = B.ACT_NBR
AND
A.TRAN_DATE=B.TRAN_DATE
AND
A.TRAN_AMOUNT =B.TRAN_AMOUNT;


I thought since I am joining back to the updated table using the three columns that make a unique row it would be okay....but I guess not. Any idea why this isn't working, and if there is a way to do this? I'm about to go postal.

 

[dnoeth]

The derived table is *not* returning one row per (act_nbr, tran_date, tran_amount), just run it. I don't know exactly what your trying to do, maybe it's enough to add a MAX or MIN(CASE...) and GROUP BY 1,2,3.

Please post some data and expected result set, it's easier to understand than narratives ;-)

Dieter

 

[WILLIEWANKA]

Good grief, are you ever wrong? I swear one day i'm going to find a problem with Teradata and not with my code. Mark my words!!!! MUHA HA HA HA HA HA.



So yeah, Dieter was right, there were 8 duplicate rows in the derived table (or subquery, never got a clear understanding of the difference hint hint hint). Apparently there is something wrong with the ETL process that loads that table. Looks like I owe you a beer Dieter