mysql - 想更新mysql数据库上的某个字段

Question

我想使用另一个表的总和来更新我表上的一个字段。波纹管是查询但不工作。

UPDATE ehap
SET this_month = SUM(beneficiaries.AMOUNT)
JOIN beneficiaries on beneficiaries.EHAP_ID = ehap.id
WHERE 
    beneficiaries.EHAP_ID = ehap.id AND
    MONTHNAME(beneficiaries.disbursement_date) = MONTHNAME(CURDATE())

this_month 是 ehap 表中的一个字段，另一个表是名称受益人。我想从每个 ehap 的受益人那里获得这些金额的总和，并将其保存到 ehap 表的 this_month 字段中。

我希望你们能理解我的问题。

标签： mysql

Answer 1

更新，我能够修复它，请参阅下面的查询：

UPDATE ehap c

INNER JOIN (    select ehap.id, SUM(beneficiaries.AMOUNT) AS Total2
    from ehap
    JOIN beneficiaries on beneficiaries.EHAP_ID = ehap.id
    where beneficiaries.EHAP_ID = ehap.id and MONTHNAME(beneficiaries.disbursement_date) = MONTHNAME(CURDATE())
    GROUP by ehap.incident_name ) y ON c.id = y.id

INNER JOIN (    select ehap.id, SUM(beneficiaries.AMOUNT) AS Total
    from ehap
    JOIN beneficiaries on beneficiaries.EHAP_ID = ehap.id
    where beneficiaries.EHAP_ID = ehap.id and MONTHNAME(beneficiaries.disbursement_date) != MONTHNAME(CURDATE())
    GROUP by ehap.incident_name ) x ON c.id = x.id

INNER JOIN ( select ehap.id, COUNT(*) as total_number
    from ehap
    JOIN beneficiaries on beneficiaries.EHAP_ID = ehap.id
    where beneficiaries.EHAP_ID = ehap.id
    GROUP by ehap.incident_name ) w ON c.id = w.id


SET c.this_month = y.Total2, c.previous = x.total, c.w_payment = w.total_number, c.on_process = c.no_benef - w.total_number, c.balance
= c.amount - (x.total + y.Total2)