'INSERT ... ON DUPLICATE KEY UPDATE Statement

There's my query :

INSERT INTO qualification_score 
   (qualification_score.Name, qualification_score.value , qualification_score.date_calcule, qualification_score.customers_id)
   SELECT
        'montant',
        c.somme_achats,
        c.calc_date,
        c.customer_id
    FROM
        customer_info c,
        qualification_score s  
    WHERE 
    c.customer_id = s.customers_id
    ON DUPLICATE KEY UPDATE qualification_score SET qualification_score.value = (SELECT SUM(o.total_paid) FROM orders o 
                       INNER JOIN order_states ON o.current_state = order_states.id
                       WHERE qualification_score.customer_id=o.customer_id AND order_states.invoice = 1)'''

This is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET qualification_score.value = (SELECT SUM(o.total_paid) FROM orders o

sqlmariadb


Solution 1:[1]

i fixed the problem:

In fact you have just to edit the DUPLICATE KEY UPDATE TO :

ON DUPLICATE KEY UPDATE qualification_score.value = (SELECT SUM(o.total_paid) FROM orders o 
                   INNER JOIN order_states ON o.current_state = order_states.id
                   WHERE qualification_score.customers_id=o.customer_id AND order_states.invoice = 1),
            qualification_score.date_calcule = NOW()

Here's all of the request :

    INSERT INTO qualification_score 
   (qualification_score.Name, qualification_score.value , qualification_score.date_calcule, qualification_score.customers_id)
   SELECT
        'montant',
        c.somme_achats,
        c.calc_date,
        c.customer_id
    FROM
        customer_info c,
        qualification_score s  
    WHERE 
    c.customer_id = s.customers_id
    ON DUPLICATE KEY UPDATE qualification_score.value = (SELECT SUM(o.total_paid) FROM orders o 
                       INNER JOIN order_states ON o.current_state = order_states.id
                       WHERE qualification_score.customers_id=o.customer_id AND order_states.invoice = 1)'''

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chris Catignani

Related Questions