Update Data trying to insert duplicates - Even after a Deduplication activity. | Community
Skip to main content
CampaignerForLife
CampaignerForLife
Level 5
April 29, 2024
Solved

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

  • April 29, 2024
  • 2 replies
  • 1748 views

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. 

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

 

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

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

 

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by _Manoj_Kumar_

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

     

    2 replies

    rahulkad29786
    rahulkad29786
    Level 4
    April 29, 2024

    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

    CampaignerForLife
    CampaignerForLifeAuthor
    Level 5
    April 29, 2024

    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!)

      rahulkad29786
      rahulkad29786
      Level 4
      April 29, 2024

      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.

      _Manoj_Kumar_
      Community Advisor
      _Manoj_Kumar_Community Advisor
      Community Advisor
      April 30, 2024

      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
        CampaignerForLife
        CampaignerForLifeAuthor
        Level 5
        April 30, 2024

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

          _Manoj_Kumar_
          Community Advisor
          _Manoj_Kumar_Community AdvisorAccepted solution
          Community Advisor
          April 30, 2024

          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
            Terms & ConditionsAccessibility statement

            Loading footer...