Update query question 1

[Queryman]

Trying to update a field in a table that gets it's value from another table providedcertain conditions are met, geeting an error, noted below.

update cba_workdb.MD_ADVANTAGE_TOTAL_CLAIMS_2002 set BRAND_FEE =
cba_workdb.ADVANTAGE_RATES_AND_FEES.BRAND_FEE
where cba_workdb.MD_ADVANTAGE_TOTAL_CLAIMS_2002.PHARMACY_ACCOUNT_NBR in (select ACCOUNT_NBR from
cba_proddb.PHARMACY_MASTER where
superchain_nbr in('00164','00209')and account_nbr in(select PHARMACY_ACCOUNT_NBR from
cba_workdb.ADVANTAGE_RATES_AND_FEES));

ERROR: CLI execute error: [NCR][Teradata RDBMS] Duplicate row error in CBA_WorkDB.MD_ADVANTAGE_TOTAL_CLAIMS_2002.



Michael

 

[tdatgod]

Hi,
You must have created your table

cba_workdb.MD_ADVANTAGE_TOTAL_CLAIMS_2002

as a SET table meaning no duplicates are allowed. Then you have 2 rows which are currently close to each other but not duplicates like

x 6
x 7

Then the update statement is causing one of them to be updated to have the exact same contents of the other row so after the update you would end up with 2 rows like

x 7
x 7

or

x 6
x 6

Since duplicates aren't allowed in your table because it was created to be a SET (UNIQUE), the transaction is Aborted leaving the table in it orignal state

x 6
x 7

----

 

[Queryman]

The table as it currently exists has no duplicates and after the updates should have no duplicates. I am only updating one field in the table, how does that create duplicates, since the unique field (account number) is not affected, I am only updating the fee?
Thanks

Michael

 

[BillDHS]

I think I have seen this error before, make sure your subqueries are not returning more than one value for cba_workdb.MD_ADVANTAGE_TOTAL_CLAIMS_2002.PHARMACY_ACCOUNT_NBR to compare to. Try adding group by clauses.

I know subqueries aren't suppose to return dups, but I remember having to make sure that dups were not in the Selects.

 

[Queryman]

I figured this out. I have to use an equal sign after the initial set statement for the where clause not an in statement.

Michael