I'm trying to understand why Marketing Cloud requires a LEFT JOIN where one key is NULL to add users to an existing Data Extension when we choose UPDATE as the processing...?

For clarity, the error received is:

Query failed during execution. Error: Violation of PRIMARY KEY constraint.

I know that the query fails because the key already exists, and the LEFT JOIN eliminates a duplicate but why is this necessary when UPDATE is chosen since that is supposed to update records if they already exist, and insert when the record is new.

Can anyone shed light on why?

  • One comment to add -- is it possible that the requirement is there only because there is the possibility that there are multiple records within that one data set? In other words, if the UPDATEs were all 100% guaranteed to be unique, then the LEFT JOIN wouldn't be necessary. Yes? Apr 3, 2018 at 2:00
  • I've had issues with this too. When I run the query the FIRST time, I typically run it as an overwrite then edit the query to an UPDATE. That seems to work for my specific use case. Apr 3, 2018 at 14:30
  • Alas, this was a secondary query that updates based on different criteria after the original overwrite query. Thanks for chiming in! Apr 4, 2018 at 16:54

1 Answer 1


This is because Update is actually an Upsert (confusing, I know!).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .