mysql如何update同一个表内带 sum 和where条件

Question

mysql如何update同一个表table1内带 sum(字段) 和where ***条件

table1内
UPDATE  `table1` 
   SET  `count` = 
                          (SELECT sum( `money` )   WHERE  `mid` = '20'   )  
   WHERE  `id` = '20'

也就是把所有table1表内
mid为20的money,
sum到id为20的
count里
我测试了好像不能运行,结果为null

Answer 1

UPDATE table1 t1
JOIN (
  SELECT mid, SUM(money) AS total_money
  FROM table1
  GROUP BY mid
) t2 ON t1.id = t2.mid
SET t1.count = t2.total_money
WHERE t1.id = 20;