Expand my Community achievements bar.

SOLVED

Update Data trying to insert duplicates - Even after a Deduplication activity.

Avatar

Level 7

Hello, I'm having a problem and I don't have any more idea of how to fix it.

 

I have a workflow that insert registries into database, first read a file, then deduplicate the data and then insert it. 

CampaignerForLife_0-1714386708073.png

The Data schema has a compound primary key of 6 different attributes, defined like this:

CampaignerForLife_1-1714386828476.png

 

Then In the deduplication, I search values deduplicating on all these fields

CampaignerForLife_2-1714386889605.png

And then in the update data, I selected record identification using reconciliation keys and selected all the primary key's attributes.

 

CampaignerForLife_4-1714386990158.png

But somehow, it keeps giving me this error: 

26-04-2024 14:45:08 ORA-210000 Oracle error: ORA-00001: unique constraint (U_ADOBECMP.IOP_PRIMARYKEY_D3F6991F) violated

 

 

I have checked and tried everything, but this error keeps happening. How can that happen if i'm deduplicating them? I tried to insert them using direct SQL code (INSERT INTO...) and it's inserted, but I've to remove "." before. Maybe it's having a problem reading it? has someone ever faced this and solved it??

 

Thank you in advance

 

@_Manoj_Kumar_ @ParthaSarathy @Parvesh_Parmar @DavidKangni

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Instead of using the SQL query to truncate table. Try using the Update data activity to delete the data and see if that works.

 


     Manoj
     Find me on LinkedIn

View solution in original post

8 Replies

Avatar

Level 4

Hi @CampaignerForLife ,
Use an enrichment activity post your data loading and map your file or incoming payload data to the target schema in that enrichment and post that use the deduplication on different parameters and finally post that add a new split to check if the records already existing in the target schema(In which you are inserting the records).
This will remove this unique constraint issue.
Hope that helps.

Thanks

Avatar

Level 7

This didn't work we're trying to recreate the workflow because it's the only one giving us errors. I think the main problem is, it's detecting as duplicates records that are not duplicated (If I generate the INSERT INTO codes, they're not duplicated!)

Avatar

Level 4

Unique constraint is when the record which you are trying to insert into the target schema is already present in it, so to have a split before it on PK is mandatory to resolve this issue.

Avatar

Level 7

But how to explain, i'll tell you the steps we did

 

 

Truncate table - Data loading - Deduplication using the primary keys - update data. This would remove like 10 % of the data, but we still get duplicates. 

 

Then I tried with the enrichment, and would insert only 1 record.

 

I've checked the data itself using Python and, after the deduplication, there is not duplicated data. 

Avatar

Community Advisor

Hello @CampaignerForLife 

 

The data you are trying to ingest already existing in the Schema.

 

If you don't want to update the existing records. Enable the rejects option it will pass the existing records to reject transition.

 

If you want to update the existing records in schema then change the operation to insert or update


     Manoj
     Find me on LinkedIn

Avatar

Level 7

Hello Manoj, the data does not exist in the schema. Before insert it, I execute a Truncate table command

Avatar

Correct answer by
Community Advisor

Instead of using the SQL query to truncate table. Try using the Update data activity to delete the data and see if that works.

 


     Manoj
     Find me on LinkedIn

Avatar

Administrator

Hi @CampaignerForLife,

Was the given solution helpful to resolve your query or do you still need more help here? Do let us know.

Thanks!



Sukrity Wadhwa