1

Can anyone help me out why the automation is failing? If I'm using Overwrite activity and query work perfectly.

When I'm trying to update the target DE it is failing. below is my query.

SELECT 
a.EmailAddress,
a.FirstName,
a.LastName,
a.Gender,
a.Hunting,
a.DrawingPainting,
a.Hiking,
a.State,
a.EmailOptin,
a.EmailOptinDate,
a.EmailOptoutDate,
a.RegistrationSource,
a.Country,
b.Mens,
b.Womens,
b.Kids,
b.EmailOptDown
FROM Master_Subscribers_Active a  INNER JOIN  Preference_Changelog b ON 

a.EmailAddress = b.EmailAddress

WHERE 

a.EmailOptin = 'True' AND b.EmailOptin = 'True' AND b.EmailOptDown = 'True'

Thanks.

3

You return multiple rows for each subscriber, as there is probably a one-to-many relationship to Preference_Changelog. So you need to ensure you select only one row. Not knowing your data model in details, it is difficult for me to advise you on how this can be achieved, as I assume data in all the rows in Preference_Changelog are of relevance for your target data extension.

| improve this answer | |
  • 2
    One option to resolve this without data remodeling could be to add another column for a random or increasing number (doesn't really matter), which you also set as Primary Key. The combination of email and rownumber then generates a composite key which is unique for every row. The column can be populated on the fly within the SQL statement. However: This only helps you when Lukas' assumption about multiple rows per subscriber is true (it sounds plausible to me) – Stephan de Paly Sep 12 '19 at 21:16

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.