mt表(receipt,receipttext,messageid)
-1 -1 1
-1 -1 2
dr表(status,statustext,messageid)
0 success 1
根据dr表中的内容,将status,statustext表中内容更新至mt表中的receipt,receipttext,条件messageid相同,(mt表中数据大于dr表中数据)如上述数据执行update之后,mt表中内容更新为:
mt表(receipt,receipttext,messageid)
0 success 1
-1 -1 2成功执行就给分
-1 -1 1
-1 -1 2
dr表(status,statustext,messageid)
0 success 1
根据dr表中的内容,将status,statustext表中内容更新至mt表中的receipt,receipttext,条件messageid相同,(mt表中数据大于dr表中数据)如上述数据执行update之后,mt表中内容更新为:
mt表(receipt,receipttext,messageid)
0 success 1
-1 -1 2成功执行就给分
update mt set (receipt,receipttext)=(select status,statustext from dr where mt.messageid=dr.messageid)
where exists(select * from dr where mt.messageid=dr.messageid);
--數據量大的話就用sp分批commit,不過mt,dr兩表中messageid列聚簇索引是應該建立的,語句改下就可以分批執行更新了,不過這樣會比只關聯messageid倏件慢,畢竟多了兩個關聯倏件,其他我也不清楚了,呵呵
update mt set (receipt,receipttext)=(select status,statustext from dr where mt.messageid=dr.messageid)
where exists(select * from dr where mt.messageid=dr.messageid and (mt.receipt<>dr.status
or mt.receipttext<>dr.receipttext))
and rownum<每次需更新的倏數;
直接用常量代替了:
where exists(select 1 from dr where mt.messageid=dr.messageid)
其实,oracle对多表update有自己比较高效的处理方案即:对视图的UPDATE语句update (
select /*+use_hash(mt,dr)*/ receipt,receipttext,status,statustext
from mt,dr where mt.messageid=dr.messageid
)
set receipt=status,receipttext=statustext;又或者、你可以用存储过程 declare
cursor c is select mt.*,mt.rowid from mt;
begin
for c1 in c
loop
update mt set (receipt,receipttext)=(select status,statustext from dr where mt.messageid=dr.messageid)
where rowid=c1.rowid and exists (select 1 from dr where mt.messageid=dr.messageid);
end loop;
end;这两种在效率上都比直接update好
你可以测试一下
又或者、你还可以用merge哦不过、效率和这两种差不了多少,甚至还低些呢
update (select /*+use_hash(wr_sms_mt,wr_sms_dr)*/receipt,receipttext,statuscode,
statustext from wr_sms_mt,wr_sms_dr where wr_sms_mt.messageid=wr_sms_dr.messagei
d) set receipt=statuscode,receipttext=statustext
*
第 1 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列我试了试图的这种,报错如上,还有不清楚/*+use_hash(wr_sms_mt,wr_sms_dr)*/这段,请指教:
update (select /*+ BYPASS_UJVC */ receipt,receipttext,statuscode,
statustext from wr_sms_mt,wr_sms_dr where wr_sms_mt.messageid=wr_sms_dr.messagei
d) set receipt=statuscode,receipttext=statustext