MySQL update 值等于select 语句的值

我们在开发中经常会遇到有时候更新的值要等于另一条查询语句的值.即update select

下面是我用户表中增加一字段(consumption_amount),统计用户消费总金额的用法:

UPDATE users u SET consumption_amount=( SELECT SUM(goods_amount) as amount FROM orders as o where u.user_id=o.user_id);

更新多个字段的时候:

UPDATE users u SET (consumption_amount,numbers)=( SELECT (SUM(goods_amount),SUM(goods_number)) FROM orders as o where u.user_id=o.user_id););`