All Forums Database
dsaini 5 posts Joined 08/11
19 Dec 2012
Merge SQL Duplicate Key error

I have two identical tables. One I treat as stagging (stg) and second as production (pr). I get a daily file which I first fastload into the stg table and then use the merge command to push data over to pr. I only have one same unique key defined on both tables (contact_object_id). Durring the merge sql run I get error message of primary key
 

Error: [Teradata Database] [TeraJDBC 14.00.00.21] [Error 2801] [SQLState 23000] Duplicate unique prime key error in DIRECT_pt_live.pt_optin_db.

SQLState:  23000

ErrorCode: 2801

 

This is not the first merge sql for me, I have other which works fine, but this time it is just not working.

 

Merge INTO DIRECT_pt_LIVE.pt_optin_db AS PR 

USING

(Select * From DIRECT_WRITER_LIVE.stg_optin_db) AS stg

 on (stg.contact_object_id = PR.contact_object_id)

WHEN MATCHED THEN UPDATE 

SET FIRSTNAME = stg.FIRSTNAME,

MIDDLENAME = stg.MIDDLENAME,

LASTNAME = stg.LASTNAME,

COMPANY_NAME = stg.COMPANY_NAME

WHEN NOT MATCHED THEN INSERT 

(stg.CONTACT_OBJECT_ID,stg.FIRSTNAME,stg.MIDDLENAME,st g.LASTNAME,stg.COMPANY_NAME);

 

stg and pr tables are multiset tables and they are both identical. 

 

CREATE MULTISET TABLE direct_pt_live.pt_optin_db ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CONTACT_OBJECT_ID DECIMAL(18,0),

      FIRSTNAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,

      MIDDLENAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,

      LASTNAME VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      COMPANY_NAME VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC

)

Unique Primary Index UPI_CONTACT_OBJECT_ID(CONTACT_OBJECT_ID);

 

do you know what I am doing wrong.

Tags:
WAQ 158 posts Joined 02/10
19 Dec 2012

One quick question, are you getting the same contact_object_id more than one in a particluar file?

WAQ 158 posts Joined 02/10
19 Dec 2012

I mean are you getting duplicate contact_object_id in a file?

dsaini 5 posts Joined 08/11
20 Dec 2012

no..it is not possible. because stg table has the UPI on the contact_object_id.
 

KS42982 137 posts Joined 12/12
20 Dec 2012

Looks like, at least one of the contact_object_id in the source file (stg) is already present in your target table (PR) (may be from previous loads). And as you have defined contact_object_id as UPI, it is not allowing to insert it again and throwing an error.

dsaini 5 posts Joined 08/11
20 Dec 2012

Right..isn't merge suppose to use the "Update set" instead of inserting new record?

KS42982 137 posts Joined 12/12
20 Dec 2012

Can you make sure that contact_object_id is unique in both stg and pr tables by taking count(*) and count(distinct contact_object_id) ? Because MULTISET and UPI together do not make sense to me. 

dsaini 5 posts Joined 08/11
09 Jan 2013

I fixed the error message. Production table had null in the key field and merge was trying to insert another null instead of updating. I removed the null value from the production table and updated the merge command to select rows where contact_object_id is not null from the staging table.
 
Thanks KS.

You must sign in to leave a comment.