0

I am trying to APPEND a data extension with data views on a weekly basis with automation studio. It returns the error "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Complaints'." I thought DISTINCT will work but it only works when i OVERWRITE my data extension.

SELECT distinct
contact.id as Id
, contact.email as email
, contact.FirstName as FirstName
, contact.status__c as AtlasRelation
, job.EmailName as EmailName
, complaint.EventDate as ComplaintDate

FROM contact_salesforce contact

INNER JOIN _Complaint complaint
ON complaint.SubscriberKey = contact.id

LEFT JOIN _Job job
on complaint.JobID = job.JobID

Can someone help me with this?

2

EDIT

Your Main issue is that you are trying to do an APPEND (add only) onto a DE without filtering out any already existing fields inside of the target DE. This will throw the error, which basically is saying we cannot add this to the DE as it already exists.

Easiest solution is to change from APPEND to UPDATE, to allow the add and update functionality.

By having it add and update, you will not need to worry about filtering out already existing records and instead they will just be updated with the new info.


//////////////////////////////////////////////////ORIGINAL ANSWER//////////////////////////////////////////////

Your issue is that Complaint Dataview can have multiple entries on a single SubscriberKey.

My best solution without any other changes to your current query would be to do:

SELECT distinct
contact.id as Id
, contact.email as email
, contact.FirstName as FirstName
, contact.status__c as AtlasRelation
, job.EmailName as EmailName
, complaint.EventDate as ComplaintDate

FROM contact_salesforce contact

INNER JOIN (
  SELECT * FROM Complaint 
  WHERE IsUnique = 'true'
) complaint

ON complaint.SubscriberKey = contact.id

LEFT JOIN _Job job
on complaint.JobID = job.JobID

You could also do:

INNER JOIN (
  SELECT DISTINCT * FROM Complaint
) complaint

as a solution if you wanted.

  • Hi Gortonington, thanks for your help. After trying both queries it still gives the same error of duplicate primary keys. – wfrankhuizen Apr 4 '18 at 13:26
  • By append do you mean Add only, or add and update? – Gortonington Apr 4 '18 at 13:35
  • Only Add new data to your target data extension. – wfrankhuizen Apr 4 '18 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.