0

Everyday we have an automation running to import new magento customers from an FTP file to our contact DataBase. Today, for the first time we had this error message.

Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'SystemData'. The duplicate key value is ([HIDEN EMAIL]@gmail.com).

And this is the query :

select
s.Magento_ID as Magento_ID,
case
when r.[Marketing Cloud_ID] is null or r.[Marketing Cloud_ID] = '' then s.EmailAddress 
else r.[Marketing Cloud_ID] end as [Marketing Cloud_ID],
s.EmailAddress as Email
from Import_Magento_Account_All s
left join SystemData r
on
s.Magento_ID = r.Magento_ID

Is checked and the supposed duplicated email is not in our data model. I'm really stuck any help will be much appreciated

Thank you!

  • 1
    what are the primary keys in your target DE? – Gortonington Oct 29 '19 at 15:19
  • Hi, It's the Marketing Cloud_ID wich actually is the email – Pierre Oct 29 '19 at 15:38
4

I know you said there are no duplicate emails, but it might be best to set up a dedupe subquery to validate this:

SELECT a.*
FROM (
    select
    s.Magento_ID as Magento_ID,
    case
    when r.[Marketing Cloud_ID] is null or r.[Marketing Cloud_ID] = '' then s.EmailAddress 
    else r.[Marketing Cloud_ID] end as [Marketing Cloud_ID],
    s.EmailAddress as Email,
    ROW_NUMBER() OVER (PARTITION BY s.EmailAddress ORDER BY s.EmailAddress) AS rn
    from Import_Magento_Account_All s
    left join SystemData r
    on
    s.Magento_ID = r.Magento_ID
) a
WHERE a.rn = 1

The above will only pull records where the email address exists once. As from your comment, I am assuming the Marketing_Cloud_ID is only emails currently, this should resolve the issue.

| improve this answer | |
  • Thank you for your help. When I add the updated code and click on the Check synthax I have this error message : An error occurred while checking the query syntax. Errors: The function 'ROW_NUMBER' must have an OVER clause with ORDER BY. – Pierre Oct 29 '19 at 17:30
  • oh sugar, my bad. Will edit my answer – Gortonington Oct 29 '19 at 19:04
  • Thanks a lot! The synthax is ok now. I'll run my test in a test Data Extension to check if it fix the problem. – Pierre Oct 29 '19 at 19:44
  • 1
    It worked! \o/ Thank you – Pierre Oct 29 '19 at 20:15

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.