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.
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.
x.EmailAddress as Email
, 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
where x.ranking = 1