1

Help with "Cannot insert duplicate key in object error";

We have 2 data extensions, the target DE has a PK for email address and the source DE has multiple instances of users submissions. I am getting the "Violation of PRIMARY KEY constraint 'PK_C1A760D747B0422FB941A2E240A7B92D'. Cannot insert duplicate key in object" error. In essence, i just want the most recent submission from a particular user in the Source De to update the target DE. this is my query:

SELECT EmailAddress as SubscriberKey, EmailAddress, CurrentBlue, CurrentRed, registered_date 
FROM Quiz_NewResults 
WHERE DATEDIFF(hour, registered_date, Getdate()) = 1 
    AND GreenType is null
2
  • Can you just confirm you meant SQL and not SOQL? Someone "corrected" it but marketing cloud uses only SQL unless I am mistaken.
    – Adrian Larson
    Sep 6 '16 at 21:49
  • 1
    SQL sir, i thought the same thing
    – Angel
    Sep 8 '16 at 0:03
1

You would need to dedupe and get the last submission.

See here sample. (Am using SQL partitioning ROW_NUMBER to get the last instance of that email address)

select W.EmailAddress as SubscriberKey, W.EmailAddress, W.CurrentBlue, W.CurrentRed, W.registered_date FROM
    ( SELECT  EmailAddress, CurrentBlue, CurrentRed, registered_date,
        ROW_NUMBER() OVER (partition by EmailAddress order by registered_date DESC) as 'n'
    FROM Quiz_NewResults 
        WHERE DATEDIFF(hour, registered_date, Getdate()) = 1 
            AND GreenType is null ) W
    where W.n=1
0

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.