I am getting an error when running the following Insert Select statment
INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME)
select DISTINCT TRIM (CM_CUST_NAME)
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
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>;
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.
Thanks for all the help, I basically resoved the issue by dropping the table an recreating it with a PK and not a UPK
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.
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