update语句问题

[peanut2008]

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)

[peanut2008]

上述都不行

[华夏小卒]

贴表结构，记录，要求

[shishui508]

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.我将编码

[peanut2008]

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.物资编码

[黄_瓜]

--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.物资编码 

[黄_瓜]

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.物资编码

[黄_瓜]

[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]
估计不行

[华夏小卒]

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.物资编码 

[华夏小卒]

[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]

对自己没信心啊