用JOIN更新MYSQL MARIA DB中的值

Question

我试图用sum函数更改列表的值，这是我的代码：

For example 
c.total = (10-2-3) - (3) 
c.total = 2

update tabC c 
JOIN tabB b ON b.c_id = c.id 
set c.total = (c.v1 - c.v2 - c.v3) - IF(sum(b.payment) is not null, sum(b.payment), 0) 
where c.id= 983;

但我得到了以下错误：

错误1111 (HY000)：组函数的无效使用

我认为错误是和，但我如何解决这个问题？

提前致谢

Answer 1

您需要加入一个使用GROUP BY的子查询。

UPDATE tabC c
LEFT JOIN (
    SELECT c_id, SUM(payment) AS total_payment
    FROM tabB
    GROUP BY c_id) AS b ON b.c_id = c.id
SET c.total = (c.v1 - c.v2 - c.v3) - IFNULL(b.total_payment, 0)
WHERE c.id = 983

Answer 2

您应该使用嵌套的子查询，其中聚合的resutl用于联接。

  update tabC c 
  JOIN  (
    select   c_id, ifnull(sum(payment),0) tot_payment
    from tabB
    group by c_id
  ) b ON b.c_id = c.id 
  set c.total = (c.v1 - c.v2 - c.v3) - b.tot_payment 
  where c.id= 983;