The Vertica Database Forums

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.

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.

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.