0

I am trying to make an SQL query from a data extension with a data view, but i can't get it to work.

SELECT 
a.id as Id
, a.email as email
, a.FirstName as FirstName

from contact_salesforce a

left join _Open o
on o.SubscriberKey = a.id

it gives an error when i do the automation:

"Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object"

Data Extension fields:

Id Text 18

Email EmailAddress 254

FirstName Text 40

  • What is the update type of your query? Do you have a primary key assigned in your target data extension? – David Devoy Mar 29 '18 at 14:39
  • It is a Query automation. Primary key is Id. – wfrankhuizen Mar 29 '18 at 14:44
  • David means, is it an Add, Update, or Overwrite query. You may want to add some context of what you're trying to accomplish with this query. Since you have a primary key, you'll only have 1 record for each subscriber tied to 1 record from the _Open data view. I think you're probably looking for a one-to-many relationship. – Tom Callahan Mar 29 '18 at 15:07
  • I'm sorry it's an update query. – wfrankhuizen Mar 29 '18 at 16:08
  • @wfrankhuizen I provided an answer below. But need to ask, what goal of your query. The use of the "left join" will output subscribers that are both found and not found in the _open data view. – David Devoy Mar 29 '18 at 18:00
2

The query specified will return multiple rows if more than one open event is found for the particular subscriber. You can add the keyword distinct to your query to insure only unique row of data are returned by your query. This will eliminate the key violation.

SELECT distinct
a.id as Id
, a.email as email
, a.FirstName as FirstName

from contact_salesforce a

left join _Open o
on o.SubscriberKey = a.id

Normally, I avoid using the distinct keyword and opt for aggregating the query results in some way. But since this query is only selecting columns from the contact_salesforce data extension, using distinct will work in this case.

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.