1

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

6
  • What is the update type of your query? Do you have a primary key assigned in your target data extension? Mar 29, 2018 at 14:39
  • It is a Query automation. Primary key is Id. Mar 29, 2018 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. Mar 29, 2018 at 15:07
  • I'm sorry it's an update query. Mar 29, 2018 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. Mar 29, 2018 at 18:00

2 Answers 2

3

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.

0
-1
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
1
  • Welcome to SFSE! Please take the tour and read How to Answer. This answer is what we call a code dump. It's a block of code with no explanation. Code dumps tend to be poor answers because they don't explain what the issue is, how to fix it, or why the fix works. A good explanation will help people understand the problem so that they can (hopefully) avoid, or at least identify, similar problems in the future. Code dumps also tend to generate a lot of back-and-forth in the comments like "that didn't work" and "try this instead", which makes it hard for others to follow.
    – Derek F
    Sep 6, 2019 at 12:14

You must log in to answer this question.

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