Mysql更新和连接查询

Question

我在用Sum更新查询时遇到了问题。我有两张桌子运动和捐款。我想更新来自某项运动的每一笔捐款的总额。

tb_campaign

1. id
2. 名字
3. total_donations

tb_donations

1. id
2. amount_donated
3. campaign_id

我的当前查询不工作。

UPDATE
    tb_campaign AS campaigns
    LEFT JOIN (
        SELECT
            sum(amount_donated) AS amount_donated
        FROM
            tb_donations
        GROUP BY
            campaign_id
    ) AS donations ON
        donations.campaign_id = campaigns.id
SET
    campaigns.total_donations = donations.amount_donated;

我收到了我的查询错误

Answer 1

如果要根据匹配的记录更新tb_campaign.total_donations中的列，请尝试以下更新查询。

UPDATE tb_campaign AS campaigns
        INNER JOIN
    (SELECT 
        campaign_id, SUM(amount_donated) AS amount_donated
    FROM
        tb_donations
    GROUP BY campaign_id) AS donations ON donations.campaign_id = campaigns.id 
SET 
    campaigns.total_donations = donations.amount_donated;

如果您需要更新tb_campaign表中与total_donations中不匹配的所有列，请尝试这样做，注意：“列total_donations不能为空”添加了合并以避免空

UPDATE tb_campaign AS campaigns
        LEFT JOIN
    (SELECT 
        campaign_id,
            COALESCE(SUM(amount_donated), 0) AS amount_donated
    FROM
        tb_donations
    GROUP BY campaign_id) AS donations ON donations.campaign_id = campaigns.id 
SET 
    campaigns.total_donations = donations.amount_donated;

Answer 2

您在子查询中错过了campaign_id，所以您的联接无法工作，因为没有donations.campaign_id

UPDATE  tb_campaign AS campaigns
LEFT JOIN (
        SELECT  campaign_id, sum(amount_donated) AS amount_donated
        FROM  tb_donations
        GROUP BY     campaign_id
    ) AS donations ON  donations.campaign_id = campaigns.id
SET    campaigns.total_donations = donations.amount_donated;

但是，由于您正在查找计算行，因此只有您的连接条件应该是内部连接。

UPDATE  tb_campaign AS campaigns
INNER  JOIN (
        SELECT  campaign_id, sum(amount_donated) AS amount_donated
        FROM  tb_donations
        GROUP BY     campaign_id
    ) AS donations ON  donations.campaign_id = campaigns.id
SET    campaigns.total_donations = donations.amount_donated;