我有两个表--pub_corpmtrl(单位物资编码),temp_corp表,其中temp_corp有字段name(物资名),no(物资编号),oldprice(原计划价),price(现计划价),物资编号是唯一的.
现在想更新Pub_corpmtrl中数据,使其price(原计划价)全部更新为temp_corp中的price字段值(现计划价),就是只要是在temp_corp中出现的物资,在pub_corpmtrl中都要改他们的价格为现计划价,如何更新啊,我的是oracle10g.
现在想更新Pub_corpmtrl中数据,使其price(原计划价)全部更新为temp_corp中的price字段值(现计划价),就是只要是在temp_corp中出现的物资,在pub_corpmtrl中都要改他们的价格为现计划价,如何更新啊,我的是oracle10g.
set p.price = (select max(t.price) from temp_corp t where t.no = p.no)
where exists(select t.price from temp_corp t where t.no = p.no);
using temp_corp t2
on (t1.no = t2.no)
when matched then
update set t1.price = t2.price;
虽然查询结果肯定是唯一的
set p.price = (select max(t.price) from temp_corp t,pub_material pm where t.no = pm.no and pm.oid =p.mtrl)
where exists(select t.price from temp_corp t,pub_material pm where t.no = pm.no and pm.oid =p.mtrl);-- or
merge into pub_corpmtrl t1
using (select t.price,pm.oid from temp_corp t,pub_material pm where t.no = pm.no) t2
on (t1.mtrl = t2.oid)
when matched then
update set t1.price = t2.price; 47522341兄应该是做数据仓库的吧,他的方案比我的要好。
恕我冒昧,改了下以满足你的要求
Oracle 10g中对Merge语句的增强
Oracle 10g中对Merge语句的增强
set p.price = (select max(t.price) from temp_corp t,pub_material pm where t.no = pm.no and pm.oid =p.mtrl)
where exists(select t.price from temp_corp t,pub_material pm where t.no = pm.no and pm.oid =p.mtrl);-- or
merge into pub_corpmtrl t1
using (select t.price,pm.oid from temp_corp t,pub_material pm where t.no = pm.no) t2
on (t1.mtrl = t2.oid)
when matched then
update set t1.price = t2.price;
我想应该是merge好,
不过没有测试过也不好下结论。
suncrafted兄的空间里不是有一篇谈它的文章吗 ^_^
我的空间只是merge的用法但对于merge和普通update的性能对比没有啊