Duplicate Merge Key error

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Duplicate Merge Key error

Post by nnani » Wed Sep 25, 2013 11:04 am

Hello All,

Has anybody come across this error
ERROR 3147: Duplicate MERGE key detected in join [(DV0.cntnt_idtn x v_temp_schema.LT_cntnt_idtn) using cntnt_idtn_b0 and subquery (PATH ID: 1)]; value [1581846,000000,LBCKTX]
1. I created a temporary table LT_cntnt_idtn
2. I then Loaded this table using copy statement.
3. Then used merge statement to update the target table DV0.cntnt_idtn using temporary table LT_cntnt_idtn.

The very first time it went fine, no errors
But second time, If I load the same file from copy statement and try merging the above error pops.

What is this cause of this error ? And any mitigation steps for this.
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Duplicae Merge Key error

Post by id10t » Wed Sep 25, 2013 11:31 am

Hi!

Look at EXPLAIN - MERGE uses in JOIN for UPDATE and like in pre-join projection it will refuse to merge if there are join key dups.

>> But second time, If I load the same file from copy statement and try merging the above error pops.
Check that "source" table (stage) has no dups, if it has - then merge will fail. Or define "ON COMMIT DELETE ROWS" for temporary table.

[UPDATE]
If for some particular join key query return 2 and more rows, so which rule to apply on condition "WHEN MATCHED THEN UPDATE SET"? Rule for first row or for second row? That is why MERGE exits with error.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Duplicae Merge Key error

Post by nnani » Wed Sep 25, 2013 3:00 pm

Thanks skwa,

I figured it out, It was due to duplicate records in the stage table

Used the update and insert statement as a workaround

Thanks. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica Error Codes”