0

I don't know how to solve this error! I'm trying to configure an automation, but when I try to save the SQL query I get the error: The query failed during execution. Error: Violation of PRIMARY KEY constraint. Unable to enter a duplicate key. This is the SQL I'm trying to automate:

SELECT jh.AccountID,
       jh.Nome AS Nome_cliente,
       jh.Consentimento_Email,
       jh.Email,
       jh.Numero_mensagem,
       jh.Data_ultima_compra,
       jh.Vai_receber_mensagem,

        CASE WHEN a.Numero_material='4001206' OR a.Numero_material='4001207' OR a.Numero_material='4001743' OR 
                  a.Numero_material='4002793' OR a.Numero_material='4005220' OR a.Numero_material='4005845' OR 
                  a.Numero_material='4005846' OR a.Numero_material='4012098' OR a.Numero_material='4015848' OR 
                  a.Numero_material='4016587' OR a.Numero_material='4001701' OR a.Numero_material='4013535' OR 
                  a.Numero_material='4016512' OR a.Numero_material='4002322' 
        THEN 1 
    END AS Template_email

 FROM [jornada_hipertensao] jh 
 INNER JOIN (
    SELECT 
        mv.AccountID,
        mh.Numero_material
    FROM Master_Vendas_Prd mv 
        INNER JOIN Master_ItensVendas_New_Prd miv ON miv.OrderId = mv.OrderID 
        INNER JOIN Medicamentos_Hipertensao mh on mh.Numero_material = miv.MaterialNumber
    )a 
    ON a.AccountID = jh.AccountID

WHERE jh.Consentimento_Email=1

Structure of "jornada_hipertensao" enter image description here

Structure of Master_Vendas_Prd enter image description here

Structure of Master_ItensVendas_New_Prd enter image description here

Structure of Medicamentos_Hipertensao enter image description here

I got lost, from what I researched this error occurs when the source of the query does not have a primary key, but the destination does.. but I'm using several tables... I don't know how to solve it, I tried to remove the primary key from the destination base but it is mandatory to have the PK in the destination base....

Destination base: enter image description here

thank you who can help

1
  • What's criteria for picking one of the duplicate AccountID rows if they are returned from your query? Dec 6, 2022 at 21:56

1 Answer 1

3

The simple answer is to partition by the primary key of the target DE, but the criteria to order the duplicates (by newID() below) so the one you want is picked is the key question.


SELECT top 1 with ties
  jh.AccountID
, jh.Nome AS Nome_cliente
, jh.Consentimento_Email
, jh.Email
, jh.Numero_mensagem
, jh.Data_ultima_compra
, jh.Vai_receber_mensagem
, case 
    when a.Numero_material in ('4001206','4002793','4005846','4016587','4016512','4001207','4005220','4012098','4001701','4002322','4001743','4005845','4015848','4013535' THEN 1 
  end AS Template_email
 FROM [jornada_hipertensao] jh 
 INNER JOIN (
    SELECT 
      mv.AccountID
    , mh.Numero_material
    FROM Master_Vendas_Prd mv 
    INNER JOIN Master_ItensVendas_New_Prd miv ON miv.OrderId = mv.OrderID 
    INNER JOIN Medicamentos_Hipertensao mh on mh.Numero_material = miv.MaterialNumber
) a ON a.AccountID = jh.AccountID
WHERE jh.Consentimento_Email=1
order by row_number() over (partition by a.accountId order by newID())
3
  • I tested your sql to see if it worked and I'm getting: Incorrect syntax near the keyword 'WITH'. Anyway, I'm starting to understand my mistake. @Adam Spriggs Dec 7, 2022 at 12:34
  • 1
    I'd recommend using a Query Activity instead of Query Studio. Dec 7, 2022 at 13:04
  • Thank you! You were right about running out of query studio :) @Adam Spriggs Dec 7, 2022 at 17:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .