一个简单的两个表，update的问题

[fanzs007]

两表数据
select goodsid ,newlsj from tmp_jgtz where goodsid in(41511,20380)
goodsid newlsj
1 41511 10.5
2 20380 7.2
3 41511 9.8
select goodsid ,price from t_good_price where goodsid in(41511,20380) and priceid=1
goodsid price
1 20380 6.00
2 41511 10.00

SQL语句
update t_good_price set price=b.newlsj
from (select newlsj,goodsid from tmp_jgtz) b where t_good_price.goodsid=b.goodsid and
t_good_price.priedid=1



如何在ORACLE上执行?

试了
update t_good_price a
set price=(select b.newlsj from tmp_jgtz b
where a.goodsid=b.goodsid and a.priceid=1
)
where exists (
select 1 from tmp_jgtz b where a.goodsid=b.goodsid
)
提示单行子查询返回多于一个行

update t_good_price
set price=tmp_jgtz.newlsj from tmp_jgtz
where t_good_price.goodsid=tmp_jgtz.goodsid


update t_good_price set price=tmp_jgtz.newlsj from t_good_price t1
inner join tmp_jgtz t2 on t1.goodsid=t2.goodsid

解决不了

[inthirties]

update t_good_price a
set price=(select b.newlsj from tmp_jgtz b
where a.goodsid=b.goodsid and a.priceid=1
)
where exists (
select 1 from tmp_jgtz b where a.goodsid=b.goodsid
)

这个出错，是不是因为确实有的记录有多条数据呀。或者你用最大值来解决

update t_good_price a
set price=(select max(b.newlsj) from tmp_jgtz b
where a.goodsid=b.goodsid and a.priceid=1
)
where exists (
select 1 from tmp_jgtz b where a.goodsid=b.goodsid
)

[ojuju10]

merge into t_good_price t1
using tmp_jgtz t2
on t1.goodsid=t2.goodsid
when matched then update set t1.price=t2.newlsj

[fanzs007]

谢谢各位.

下面code可以
update t_good_price a
set price=(select b.newlsj from tmp_jgtz b
where a.goodsid=b.goodsid and a.priceid=1
and b.djbh in(select max(djbh) from tmp_jgtz)
)
where exists (
select 1 from tmp_jgtz b where a.goodsid=b.goodsid
)

[Jane_64]

update t_good_price
set price = (select tmp_jgtz.newlsj from t_good_price,tmp_jgtz
where t_good_price.goodsid = tmp_jgtz.goodsid
and t_good_price.priedid = 1);

[zWX19940]

rownum

[zWX19940]

update t_good_price a
set price=(select b.newlsj from tmp_jgtz b
where a.goodsid=b.goodsid and a.priceid=1 and rownum=1)