update语句问题

peanut2008 2009-09-02 12:06:30
select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码

现在要把上面算出来的jhdj替换到 物资库 中,代码如下,不太会写了,请大家指教:
update 物资库
set 计划单价= jq.jhdj
where jq.jhdj in ((select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) jq)
...全文
89 9 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
华夏小卒 2009-09-02
[Quote=引用 3 楼 beirut 的回复:]
引用 2 楼 beirut 的回复:
SQL codeupdate 物资库set 计划单价=select
    kje+yje)/(kcsl+rks)as jhdjfrom 
(select 物资编码,库存数量as kcsl,库存数量*计划单价as kjefrom 物资库) ajoin
(select 物资编码 ,sum(入库数)as rks ,sum(入库数*实际单价)as yjefrom 验收groupby 物资编码) b¡­

估计不行
[/Quote]

对自己没信心啊
  • 打赏
  • 举报
回复
华夏小卒 2009-09-02

update 物资库
set 计划单价=t.jhdj
from 物资库 m,
(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) t
on
a.物资编码 = b.物资编码
)t
where t.物资编码 =m.物资编码
  • 打赏
  • 举报
回复
小_爱 2009-09-02
[Quote=引用 2 楼 beirut 的回复:]
SQL codeupdate 物资库set 计划单价=select
kje+yje)/(kcsl+rks)as jhdjfrom
(select 物资编码,库存数量as kcsl,库存数量*计划单价as kjefrom 物资库) ajoin
(select 物资编码 ,sum(入库数)as rks ,sum(入库数*实际单价)as yjefrom 验收groupby 物资编码) b¡­
[/Quote]
估计不行
  • 打赏
  • 举报
回复
小_爱 2009-09-02
update 物资库 
set 计划单价=select
kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码
  • 打赏
  • 举报
回复
小_爱 2009-09-02
--try
update 物资库
set 计划单价=select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码
  • 打赏
  • 举报
回复
peanut2008 2009-09-02

update 物资库
set 计划单价=ab.jhdj
from 物资库,(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) as ab where 物资库.物资编码 = ab.物资编码
  • 打赏
  • 举报
回复
shishui508 2009-09-02

update a set a.计划单价=c.jhdj from 物资库 a,(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) jq)) c where a.物资编码=b.我将编码
  • 打赏
  • 举报
回复
华夏小卒 2009-09-02
贴表结构,记录,要求
  • 打赏
  • 举报
回复
peanut2008 2009-09-02
上述都不行
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2009-09-02 12:06
社区公告
暂无公告