All Forums Database
donal28 9 posts Joined 06/12
12 Jun 2012
Error from Insert Select statement

Hi All

I am getting an error when running the following Insert Select statment

Statement

INSERT INTO  DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME)

 

select  DISTINCT TRIM (CM_CUST_NAME)

FROM DDHWV03P.CM_CUSTOMER_STATIC_PERIODIC

 

Error

INSERT Failed. 2801:  Duplicate unique prime key error in DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO. 

Neither the BORROWER_NAME or CM_CUST_NAME fields are prime keys and both are the same datatype varchar(255)

 

Would I better using Update Select statement instead? Do I need to join the tables to do this

Any help on this would be very much appreciated

Regards

Donal

venkylingutla 19 posts Joined 06/12
12 Jun 2012

Hi,

It means in the table FMP_STRATEGIC_CM_BORROWER_AUTO the colmn contains unique primary index please check. with the sql statement

show table <tablename>;

thanks,

Venkat

ulrich 816 posts Joined 09/09
13 Jun 2012

you said that BORROWER_NAME is not part of the PK, which you seem to have defined for the table.

In this case your statement will use null for all  DISTINCT TRIM (CM_CUST_NAME)

And if you have more then one you would have a duplicate PK...

So you would need to give the PK a unique not null value.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

donal28 9 posts Joined 06/12
13 Jun 2012

Hi Guys

 

Thanks for all the help, I basically resoved the issue by dropping the table an recreating it with a PK and not a UPK

 

Donal

ulrich 816 posts Joined 09/09
13 Jun 2012

You might avoid the error message but I doubt that you resolved the issue. 

You will face skew issues if the Primary Index is Null for all rows.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

donal28 9 posts Joined 06/12
20 Jun 2012

Thanks for the advice ulrich what would you recommend to totally resolve the issue?

dw.aravind 2 posts Joined 12/12
03 Jul 2015

Weird UPI violation 2801 issue while doing INSERT SELECT operation,
Source table is defined as UPPI with  4 columns and target table is defined same as UPPI with columns while performing INSERT SELECT I am getting UPI violation.
 
So when changed the target table with PPI( removing Unique) then it gets inserted, to my surprise I see duplicates based on the 4 columns(performed group by on 4 columns)  but the source is defined as as unique how can this be  . I am wondering . Can anyone answer this please
 

ToddAWalter 316 posts Joined 10/11
04 Jul 2015

Different data types? 

dw.aravind 2 posts Joined 12/12
09 Jul 2015

I have even checked the data types .. same in both the tables

You must sign in to leave a comment.