0

We are doing a big SQL that combines data from 3 synced data extensions (objects from CRM) and 3 local data extensions in Marketing Cloud. The data lands in the target DE, that's OK. However, we need to do a second step/SQL to get additional data from one of the DE used in the first SQL. The reason for this is that a single contact (i.e.company employee) does not have have a value for the account_id column, unless his title is company director or owner, i.e. only company directors have this value in their Account_ID field. In reality, we are trying to join the new DE with one of the initial DEs to populate this data for company employees as well, meaning to get the Account_ID data from company directors into company employees. The 2 DEs have different PrimaryKeys. Each company / group of employees + director share the identical value for the Customer_ID field.

SELECT a.FirstName , a.LastName , a.Email , a.CompanyName , a.Title , a.Customer_ID , b.Account_ID FROM Send_Audience AS a INNER JOIN Customers AS b ON a.Customer_ID = b.Customer_ID.

How to achieve this without getting PrimaryKey violation? Meaning I just need the Account_ID updated, that's all.

  • Have you tried doing this in steps? You might create another data extension as a staging DE to help. – garek007 Nov 14 '19 at 18:41
  • You mean getting the contacts whose Account_ID IS NOT NULL? Something along those lines? – nix9247 Nov 14 '19 at 18:48
  • You are getting a PK violation probably because you are trying to do an update operation and the results are not fitting. I cannot be sure because I haven't seen your table structure or SQL, but sometimes using staging tables can help. If you post more info, we may be able to better help you – garek007 Nov 14 '19 at 20:44
  • garel007 I've removed the primary key from a target data extension to avoid violation. I'm just interested in the approach - can I make the join based on the Customer_ID to get the data I'm missing from the first SQL? How will SFMC treat this - will it return all records where there's a match or will it update the Account_ID field where it's missing? – nix9247 Nov 14 '19 at 21:28
4

Based on my assumption without seeing your DE structures or Primary Keys but reading your query and explanation, the issue you are facing is you are attempting an INNER JOIN, this join is great for a 1:1 relationship when joining using unique identifiers because you will return only matching results and the fact that your target DE can only find one match and your resulting DE has a primary key with a type update, it works well and you won't experience a primary key violation.

In your scenario, It sounds like your INNER JOIN in conjunction with your relationship joining values are finding multiple matches and therefore returning multiple results which result in a primary key violation.

I would recommend doing 2 things, either de-dupe using a staging DE by determining the most relevant record to join on based on the business requirements or perform a Row Number Partition on a LEFT JOIN query to prevent returning a many result by ordering and selecting Row 1 and only returning the results from the Send_Audience DE along with 1 result from your Customers DE. See example below using Row Number Partition. (Note: I have not validated the SQL syntax)

SELECT 
  a.FirstName, 
  a.LastName, 
  a.Email, 
  a.CompanyName, 
  a.Title,
  a.Customer_ID, 
  b.Account_ID 
FROM Send_Audience a 

LEFT JOIN 
  (
    SELECT 
      Account_ID,
      ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY [ENTER SOME FIELD HERE TO ORDER PRIORITY] ASC) AS Row
    FROM Customers
  ) b
ON a.Customer_ID = b.Customer_ID

WHERE b.Row = 1

If you require to return only results that match on your Customers DE (to mimic the INNER JOIN behaviour), you can add an additional clause, see below.

b.CUSTOMER_ID IS NOT NULL
| improve this answer | |
  • Wow, huge thanks!! Most importantly is that your subquery returns a record that has Account_ID NOT NULL and combines it with the main query, meaning, copies the Account_ID value fetched from the subquery into all the empty Account_ID fields of the employees in the resulting DE. – nix9247 Nov 15 '19 at 5:49
  • Happy to help, hope that solves your overall problem and hope that you can accept the answer if satisfied. – thechrishaddad Nov 15 '19 at 5:53

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.