mysql - 在更新查询中组函数的使用无效

Question

有人可以帮我看看问题出在哪里吗我在下面的查询中收到“组函数的无效使用”

UPDATE t1 
JOIN t2 ON t1.id=t2.id
SET t1.total_amount = SUM(IF((t2.`due` <= 0), t2.`amount`, 0))
WHERE t2.flag=1 AND t2.id=003;

Answer 1

您想要在子查询中聚合，然后在外部查询中联接和设置:

update t1
inner join (
    select id, sum(case when due < 0 then t2.amount else 0 end) total_amount
    from t2
    where id = 3 and flag = 1
    group by id
) t2 on t1.id = t2.id
set t1.total_amount = t2.total_amount