1

We have a daily Update query activity that is failing because of a primary key violation on the email field. I'm confused by this because I thought an Update activity should, well, update the target data extension when a matching primary key is found. Am I missing something? Here are screenshots of the error and the activity summary. Thank you in advance for any help.

Error message

Activity summary

  • What are the Pkeys on your DE? – Gortonington Oct 29 '18 at 14:24
  • Hi @Gortonington, the primary key is Email. Thanks. – Rocky Oct 29 '18 at 15:10
  • Is this the same Pkey as what is on your Source DE? – Gortonington Oct 29 '18 at 19:27
  • i have tested a similar query without issues. try a. removing your where clauses one by one. b. creating a new target de (sometimes DEs get funky over time) c. add subkey as a pkey to your target DE. – Gortonington Oct 30 '18 at 12:53
  • Try using a GROUP BY statement in your Query GROUP BY pc.EmailAddress – Gortonington Oct 31 '18 at 12:47
2

I've got this working, but first thanks to Matthew and Gortonington for their suggestions. Salesforce support confirmed that the problem was exactly as Matthew suggested: Even though the activity runs as an update, if the SQL returns duplicate values for the primary key within a single run then the activity will fail.

But Salesforce wasn't able to recommend a solution. I finally found it in Adam Spriggs' blog, under the heading "Primary key violation": Signature Moves

Here is the modified query, which dedupes using a subquery to order entries by last modified date and then selects only one of them.

select
x.EmailAddress as Email
, x.SubscriberKey
from (
  select
  w.EmailAddress
  , w.SubscriberKey
  , w.LastModified
  , row_number() over (partition by w.EmailAddress order by w.LastModified asc) ranking
  from ent.PC_CustomerOptins_AICPA w
  WHERE w.AICPA_Tax_Insider = 'True'
    AND w.EmailAddress IS NOT NULL
) x
where x.ranking = 1
  • One last comment to all this: I realized I should grab the most recent entry, so in production I changed subquery to sort descending. – Rocky Oct 31 '18 at 17:24
  • @adamspriggs answering questions in SFSE without even posting an answer. love it. ha ha – Gortonington Oct 31 '18 at 19:11
0

I am guessing that your Query is returning more than one result per Primary Key, and it doesn't know which record should be used to update your row in your data extension. If it's finding more than one record, you might need to include a DISTINCT clause.

  • Thank you, Matthew, for the suggestion but unfortunately that didn't fix it. – Rocky Oct 29 '18 at 14:58
  • What's your key field? – Matthew Wash Oct 29 '18 at 15:16
  • Well that could certainly do it. You may have a case where you have duplicate emails but separate subscriber keys. – Matthew Wash Oct 29 '18 at 18:10
  • Hi Matthew, I'm sorry but I'm not following. If Email is the primary key, shouldn't the query update the DE when a matching (i.e., duplicate) Email is found? – Rocky Oct 29 '18 at 18:23
  • Maybe I'm misunderstanding - if your email is set as the primary key in your data extension, but you're also selecting Subscriber Key, it's possible you are trying to enter two records with the same Email Address but different subscriber keys. You can test this by creating a different data extension that doesn't have a primary key rule, put your SELECT results there, and see where your duplicates are. – Matthew Wash Oct 29 '18 at 18:29

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.