I have a query in Marketing Cloud to get all contacts from a specific list of accounts:
SELECT contact.id as ContactID, contact.email as Email, accounts.AccountID as AccountID FROM Contact_Salesforce as contact INNER JOIN ProductA_customers as accounts ON contact.AccountId=accounts.AccountID
The target data extension is configured with "ContactID" as Primary Key and all other fields as nullable. The Data Action on the query is set to "Update".
When I try to run this, I get the following error:
Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'ProductA_contacts'. The duplicate key value is (0030O00002524v7QAA).
The table "ProductA_customers" may have multiple rows for each AccountID, so my guess is that this is causing the error, but I'm not sure how to get around it.
If Account X has two contacts, and Account X is in "ProductA_customers" three times, the two contacts should be added/updated to the target data extension.