1

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]
1

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.

  • Up vote - thank you... unfortunately I'm new vote - my vote cast only recorded but not display. – cblemuel Aug 15 '18 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.