update medstore.t_store_goods
set totalnow=a.total,
sumnow=a.sum
from (select goodsid,sum(total) total,sum(cost) sum
from medstore.t_store_goods_detail
here storeid=1 group by goodsid) a
where goodsid=a.goodsid and storeid=1
set totalnow=a.total,
sumnow=a.sum
from (select goodsid,sum(total) total,sum(cost) sum
from medstore.t_store_goods_detail
here storeid=1 group by goodsid) a
where goodsid=a.goodsid and storeid=1
set (
b.totalnow,b.sumnow
)=
(
select a.total_cost,a.sum_cost
from (
select goodsid,sum(total) total_cost,sum(cost) sum_cost
from medstore.t_store_goods_detail
where storeid=1
group by goodsid
) a
where b.goodsid=a.goodsid
)
where b.storeid=1
set totalnow=a.total_num,
sumnow=a.sum_num
from (select goodsid,sum(total) total_num,sum(cost) sum_num
from medstore.t_store_goods_detail
where storeid=1 group by goodsid) a
where goodsid=a.goodsid and storeid=1
oracle 与 sql server 的写法有很多不同。
update medstore.t_store_goods b
set b.totalnow=(select total from
(select goodsid,sum(total) total,sum(cost) sum
from medstore.t_store_goods_detail
where storeid=1 group by goodsid) a
where a.goodsid=b.goodsid
),
b.sumnow=(select sum from
(select goodsid,sum(total) total,sum(cost) sum
from medstore.t_store_goods_detail
where storeid=1 group by goodsid) c
where c.goodsid=b.goodsid
)
where b.storeid=1