sql根据一个表数据求和去修改另外一个表数据某一个字段

假设有两张表商品表tb_goods,每个人提货明细表tb_pick_up_order,修改tb_goods表中提货总量,提货总量是根据tb_pick_up_order算出来的.

1. 计算商品提货总量(提货审核通过状态)

        SELECT h.goods_code,sum(pick_up_quantity) FROM tb_pick_up_orderh WHERE h.goods_code=g.goods_code and h.status='P' group by h.goods_code

        2. 更新tb_goods表中提货总量字段

        update  set pickup_quantity =.....

合起来一条语句是

UPDATE tb_goods g INNER JOIN
tb_pick_up_order h ON h.goods_code=g.goods_code
SET g.pickup_quantity = (SELECT sum(pick_up_quantity) FROM tb_pick_up_order h WHERE h.goods_code=g.goods_code and h.status='P' group by h.goods_code);