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

Improve this question
5
  • What are the Pkeys on your DE?
    – Gortonington
    Commented Oct 29, 2018 at 14:24
  • Hi @Gortonington, the primary key is Email. Thanks.
    – Rocky
    Commented Oct 29, 2018 at 15:10
  • Is this the same Pkey as what is on your Source DE?
    – Gortonington
    Commented Oct 29, 2018 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
    Commented Oct 30, 2018 at 12:53
  • Try using a GROUP BY statement in your Query GROUP BY pc.EmailAddress
    – Gortonington
    Commented Oct 31, 2018 at 12:47

2 Answers 2

Reset to default
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
Improve this answer
2
  • 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
    Commented Oct 31, 2018 at 17:24
  • 1
    @adamspriggs answering questions in SFSE without even posting an answer. love it. ha ha
    – Gortonington
    Commented Oct 31, 2018 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.

Improve this answer
7
  • Thank you, Matthew, for the suggestion but unfortunately that didn't fix it.
    – Rocky
    Commented Oct 29, 2018 at 14:58
  • What's your key field?
    – Matthew Wash
    Commented Oct 29, 2018 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
    Commented Oct 29, 2018 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
    Commented Oct 29, 2018 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
    Commented Oct 29, 2018 at 18:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .