Updating records from the base DE in Marketing Cloud

Question

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.

Answer 1

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