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.
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.