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

Question

Every day a file is dropped on an FTP which consists of people with their relationnumbers (relatienummer) and the products (documentnummer) that they have bought. However, the same person can buy multiple products. I have a journey that is only for people that buy 1 specific product in the file and I want to select these people and insert them into a Data Extension in which relation number is the identifier.

This is my query:

SELECT DISTINCT m.Relatienummer, 
       m.Aanmaakdatum,
       m.Documentnummer
FROM Acquisitie_Campagne_Testrapporten_PROD m
WHERE  Artikelcode = 'DOS006' AND m.Aanmaakdatum = Cast(Dateadd(dd, -1, Getdate())as date)

Query failed during execution.

Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Acquisitie_Campagne_Main_Contacts_PROD'. The duplicate key value is (0001423---).

Can someone help me with this error?

Answer 1

Generally speaking you'd use a T-SQL windowing function to de-duplicate the rows.

If your primary key in the target data extension is only Relatienummer, then you can partition by that field and order the duplicates by Aanmaakdatum. The row_number() numbers them in order. The where-clause in the outer query picks the first one for each Relatienummer.

select 
  x.Relatienummer
, x.Aanmaakdatum
, x.Documentnummer
from (
    SELECT 
      m.Relatienummer
    , m.Aanmaakdatum,
    , m.Documentnummer
    , row_number() over (partition by m.Relatienummer order by m.Aanmaakdatum asc) ranking
    FROM Acquisitie_Campagne_Testrapporten_PROD m
    WHERE  
    m.Artikelcode = 'DOS006' 
    and m.Aanmaakdatum >= convert(date,getDate()-1)
    and m.Aanmaakdatum < convert(date,getDate())
) x 
where x.ranking = 1

I have a list of fixes for the 4 types of SFMC query errors on my blog.