삽입...키 업데이트 문 복제 시

삽입...키 업데이트 문 복제 시

질문이 있습니다.

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)'''

오류는 다음과 같습니다.

SQL 구문에 오류가 있습니다. 'SET qualification_score.value =(SELECT SUM(o.total_payed) FROM 주문에서 사용할 올바른 구문은 MariaDB 서버 버전에 해당하는 설명서를 확인하십시오.

나는 문제를 고쳤습니다.

실제로 복제 키 업데이트를 다음으로 편집하기만 하면 됩니다.

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()

다음은 모든 요청 사항입니다.

    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)'''

언급URL : https://stackoverflow.com/questions/72183823/insert-on-duplicate-key-update-statement