[SalesForce] Violation of PRIMARY KEY constraint. Cannot insert duplicate key

Getting error on the query and is below.

Email_Master primary key is 'Email Address' and thee target Data Extension primary key is also 'Email Address'. Can you help/advise what is the issue on the query statement?

ERROR: Query failed during execution. Error: Violation of PRIMARY KEY
constraint. Cannot insert duplicate key in object 'In Email_Master
_subscribers Status'

SELECT 
a.[Email Address] as 'Email Address', 
a.[Subscriber Key] as 'Subscriber Key', 
b.status as 'Engagement',
b.SubscriberID,
b.DateUndeliverable,
b.DateUnsubscribed,
b.DateJoined,
b.BounceCount,
b.SubscriberType,
b.Locale,
b.Domain
FROM [Email_Master] as a
JOIN [_Subscribers] as b
ON a.[Email Address] = b.[EmailAddress]

Best Answer

Your join is resulting for multiple subscribers from _Subscribers, so you'll need to handle that case and pick only one of the duplicates to return.

I'd handle it with a windowing function:

select 
  x.[Email Address] 
, x.[Subscriber Key] 
, x.Engagement 
, x.SubscriberID
, x.DateUndeliverable
, x.DateUnsubscribed
, x.DateJoined
, x.BounceCount
, x.SubscriberType
, x.Locale
, x.Domain
from (
    SELECT 
      a.[Email Address]
    , a.[Subscriber Key]
    , b.status as 'Engagement'
    , b.SubscriberID
    , b.DateUndeliverable
    , b.DateUnsubscribed
    , b.DateJoined
    , b.BounceCount
    , b.SubscriberType
    , b.Locale
    , b.Domain
    , row_number() over (partition by a.[email address] order by b.dateJoined desc) rowNum
    FROM [Email_Master] as a
    JOIN [_Subscribers] as b ON a.[Email Address] = b.[EmailAddress]
) x
where x.rowNum= 1

The innermost query assigned a number for each of the rows by email address, the order of the duplicates is specified by the order by clause. The outermost query just picks those that have a rowNum of 1.

Related Solutions

[SalesForce] ExactTarget : Violation of PRIMARY KEY constraint . Cannot insert duplicate key

The issue lies in UpsertData("DE", 1, "emailaddress", ...

What UpsertData is doing is building the WHERE clause. The "1" tells how many fields following the "1" will be in the WHERE clause, the remaining fields would be updated. If you had a "2", then emailaddress and firstname would be the WHERE clause, and the remaining fields would be updated. You want that WHERE to be something unique, like a primary key, or a unique set of values. Swap out custno with emailaddress.

[SalesForce] Marketing Cloud: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Generally speaking you'd use a T-SQL windowing function to de-duplicate the rows.

If your primary key in the target data extension is only Relatienummer, then you can partition by that field and order the duplicates by Aanmaakdatum. The row_number() numbers them in order. The where-clause in the outer query picks the first one for each Relatienummer.

select 
  x.Relatienummer
, x.Aanmaakdatum
, x.Documentnummer
from (
    SELECT 
      m.Relatienummer
    , m.Aanmaakdatum,
    , m.Documentnummer
    , row_number() over (partition by m.Relatienummer order by m.Aanmaakdatum asc) ranking
    FROM Acquisitie_Campagne_Testrapporten_PROD m
    WHERE  
    m.Artikelcode = 'DOS006' 
    and m.Aanmaakdatum >= convert(date,getDate()-1)
    and m.Aanmaakdatum < convert(date,getDate())
) x 
where x.ranking = 1

I have a list of fixes for the 4 types of SFMC query errors on my blog.

Best Answer

Related Solutions

[SalesForce] ExactTarget : Violation of PRIMARY KEY constraint . Cannot insert duplicate key

[SalesForce] Marketing Cloud: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Related Topic