Marketing Cloud SQL query update is giving error

Question

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.

Answer 1

Distinct wont work as it ensures the whole row is unique rather than just the primary key/ContactID.

It sounds like you don't necessarily need a update, you just need one version of the ContactID regardless of which account it's in, since the ContactID is the primary key.

I'd suggest to dedupe using the partition by function. See my answer here where I gave a example. SQL Deduplication Answer