3

I have a query that I am running that joins a few other data extensions and filters based on Role and Created_At date.

Also I need to remove subscribers that are in this group that are also in another data extension [Squadlockers].

When I attempt to run an update query, I get the following error:

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

SQL Query:

 SELECT
        Dusr.Contact_Key AS USR_Contact_Key,
        Dusr.Email AS USR_Email,
        Dusr.First_Name AS USR_First_Name,
        Dusr.Last_Name AS USR_Last_Name,
        Dusr.Created_At AS USR_Created_At,
        Dusr.Last_Login_At AS USR_Last_Login_At,
        Dusr.Phone_Number AS USR_Phone_Number,
        UTR.Role as UTR_Role,
        UTR.Season_Team_Id as UTR_Season_Teams_Id,
        sn_tms.Team_Id AS SN_TMS_Team_Id,
        sn_tms.Name AS SN_TMS_Name,
        sn_tms.Is_Current_Season AS SN_TMS_Is_Current_Season,
        sn_tms.Created_At AS SN_TMS_Created_At,
        sn_tms.Updated_At AS SN_TMS_Updated_At,
        sn_tms.Id AS SN_TMS_Id,
        sn_tms.Roster_Count AS SN_TMS_Roster_Count,
        tms.Id as tms_Id,
        tms.Image as tms_Image



    FROM 
    [Deliverable_Users] Dusr
    INNER JOIN ENT.[User_Team_Roles] UTR ON Dusr.Contact_Key = UTR.Contact_Key
    INNER JOIN ENT.[Season_Teams] sn_tms ON UTR.Season_Team_Id = sn_tms.Id
    INNER JOIN ENT.[Teams] tms ON sn_tms.Team_Id = tms.Id
    LEFT JOIN ENT.[Squadlockers] sl ON Dusr.Contact_Key = sl.Contact_Key

    WHERE UTR.Role LIKE 'Coach'
    AND sn_tms.Created_At >= DATEADD(day, -120, cast(GETDATE() AS DATE))
    AND sl.Contact_Key IS NULL

Here's the DE depiction:

USERSSquadlockers

  • Your error message says it all. You should dedupe by selecting distinct – Samuel Dare Aug 9 '17 at 17:23
  • You are trying to add the same record twice into a data extension that has a primary key. Either remove the primary key from the data extension , or select distinct – Samuel Dare Aug 9 '17 at 17:24
  • Which column is the primary key? – Samuel Dare Aug 9 '17 at 17:26
  • On [Deliverable_Users] it's the Contact_Key. On [Squadlockers] it's one to many which includes the Contact_Key – Tom Callahan Aug 9 '17 at 17:34
  • added images to original post – Tom Callahan Aug 9 '17 at 17:42
1

The issue is that your current query is trying to insert more than one USR_Contact_Key in to the Target DE.

Try this :

SELECT x.*

FROM 

(SELECT
        Dusr.Contact_Key AS USR_Contact_Key, /* Primary Key */
        row_number()over(partition by Dusr.Contact_Key order by Dusr.Contact_Key DESC) AS row,
        Dusr.Email AS USR_Email,
        Dusr.First_Name AS USR_First_Name,
        Dusr.Last_Name AS USR_Last_Name,
        Dusr.Created_At AS USR_Created_At,
        Dusr.Last_Login_At AS USR_Last_Login_At,
        Dusr.Phone_Number AS USR_Phone_Number,
        UTR.Role as UTR_Role,
        UTR.Season_Team_Id as UTR_Season_Teams_Id,
        sn_tms.Team_Id AS SN_TMS_Team_Id,
        sn_tms.Name AS SN_TMS_Name,
        sn_tms.Is_Current_Season AS SN_TMS_Is_Current_Season,
        sn_tms.Created_At AS SN_TMS_Created_At,
        sn_tms.Updated_At AS SN_TMS_Updated_At,
        sn_tms.Id AS SN_TMS_Id,
        sn_tms.Roster_Count AS SN_TMS_Roster_Count,
        tms.Id as tms_Id,
        tms.Image as tms_Image



    FROM 
    [Deliverable_Users] Dusr
    INNER JOIN ENT.[User_Team_Roles] UTR ON Dusr.Contact_Key = UTR.Contact_Key
    INNER JOIN ENT.[Season_Teams] sn_tms ON UTR.Season_Team_Id = sn_tms.Id
    INNER JOIN ENT.[Teams] tms ON sn_tms.Team_Id = tms.Id
    LEFT JOIN ENT.[Squadlockers] sl ON Dusr.Contact_Key = sl.Contact_Key

    WHERE UTR.Role LIKE 'Coach'
    AND sn_tms.Created_At >= DATEADD(day, -120, cast(GETDATE() AS DATE))
    AND sl.Contact_Key IS NULL) x

WHERE x.row = 1

Should get rid of your error.

  • Stands to reason, but that'll kick back a syntax error because of the * (since there are Joins) – Tom Callahan Aug 16 '17 at 19:13
  • no it won't if we use an alias. Updated answer – Samuel Dare Aug 16 '17 at 19:22
  • 1
    or your intial code would run if you remove the primary key restriction from the DE – Samuel Dare Aug 16 '17 at 19:34
  • Syntax error. USR_Contact_Key within the row_number() line should be Dusr.Contact_Key – Tom Callahan Aug 16 '17 at 19:57
3

To retrieve a single record for a one to many relationships there are a few things you should consider.

How relevant are the many records as individual records?

If you have no care about which many records you join but really just want a single record result, then I would use DISTINCT as this will be your quickest solution.

Do you need a specific record from the many records?

If you do care about returning the relevant single record from many records then you should really identify the differences between the many records and tie down the data correctly whether it requires a bunch of conditions or not is up to you or the client to identify how the data can be tied down to a single unique 1:1 record.

NOTE: If you do not tie down your data correctly, an INNER JOIN does not force a return of a 1:1 result, it will simply just return duplicate results because the INNER JOIN found that more than 1 record fit the criteria in your query.

There must have been more than 1 many records where these criteria were met and that's why you ended up with duplicate records as a final result:

WHERE UTR.Role LIKE 'Coach'
AND sn_tms.Created_At >= DATEADD(day, -120, cast(GETDATE() AS DATE))
AND sl.Contact_Key IS NULL
  • Is using the INNER JOIN the wrong way to go on this? Or is the issue that there is a User that can be associated with multiple stores and the Update data action cannot intake multiple [Deliverable_Users] being the Primary_Key of the target DE? – Tom Callahan Aug 16 '17 at 16:58
  • 1
    Its not the wrong way if you define enough where clauses that correctly tie down to a 1:1 relationship. Yes your issue is you have many deliverable users with a primary key on the target DE and it returns duplicate Primary Key Records because you are also joining 3 other tables that could start to get messy, find what other unique values you have that can return your 1:1 between your all the tables by assessing your data – thechrishaddad Aug 16 '17 at 22:13

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.