新增入库凭证时更新库存平均单价的SQL报错求助

场景与需求 #

现有两张数据表：

• articles（ID, Qte_stock, PU）：库存商品表，ID是商品主键，Qte_stock为当前库存数量，PU为商品单价
• DETAILS_BON_ACHAT（ID, qte, prix, id_article）：入库凭证明细表，qte是本次入库数量，prix是本次入库单价，id_article关联articles表的ID

需求：新增入库凭证（对应id_bon='1'的记录）时，针对DETAILS_BON_ACHAT的每条记录，更新articles表的PU字段，新单价为加权平均价，计算公式：
((D.prix*D.qte)+(A.PU*A.Qte_stock))/(D.qte+A.Qte_stock)

原代码与报错 #

尝试的SQL代码：

/*articles(ID,Qte_stock,PU)*/
/*DETAILS_BON_ACHAT(ID,qte,prix,id_article)*/
USE bitsclic_easy_erp;
UPDATE articles set PU=(
    SELECT ((D.prix*D.qte)+(A.PU*A.Qte_stock))/(D.qte+A.Qte_stock)
    FROM DETAILS_BON_ACHAT D 
    INNER JOIN articles A
    ON D.id_article = A.ID
    WHERE D.id_bon='1' 
    GROUP BY D.id_article
    );

报错信息（翻译后）：

错误代码：1093. 不能在FROM子句中指定更新的目标表 'articles'

问题原因 #

MySQL不允许在UPDATE语句的SET子句的子查询中直接引用被更新的目标表，因为这会引发数据读取和更新的冲突——数据库无法确定是先读取旧数据还是先应用更新，从而抛出该错误。

解决方案 #

采用UPDATE...JOIN语法直接关联两张表进行更新，避免子查询中引用目标表的问题。

方案1：直接关联更新（适用于同入库单同商品仅一条记录） #

USE bitsclic_easy_erp;
UPDATE articles A
JOIN DETAILS_BON_ACHAT D ON A.ID = D.id_article
SET A.PU = ((D.prix * D.qte) + (A.PU * A.Qte_stock)) / (D.qte + A.Qte_stock)
WHERE D.id_bon = '1';

方案2：先汇总再更新（适用于同入库单同商品多条记录） #

如果同一入库单中同一个商品有多条入库记录，先汇总该商品的总入库数量和总金额，再计算加权平均价，避免重复计算：

USE bitsclic_easy_erp;
UPDATE articles A
JOIN (
    SELECT 
        id_article, 
        SUM(qte) AS total_qte, 
        SUM(prix * qte) AS total_amount
    FROM DETAILS_BON_ACHAT
    WHERE id_bon = '1'
    GROUP BY id_article
) D ON A.ID = D.id_article
SET A.PU = (D.total_amount + A.PU * A.Qte_stock) / (D.total_qte + A.Qte_stock);

内容的提问来源于stack exchange，提问作者hamza meliki