表A(ID1,VALUE)
表B(ID2,ID3)描述:我想批量更新表A的所有value字段, 如果ID1=ID2, 就把 id3更新到value字段
常规方法:
update a
set a.value= (select id3 from b where a.id1 = b.id2)
where exists (select 1 from b where a.id1 = b.id2 )但是表中的数据有几千万,,,到不过来的,,,请问能不能用循环更新1000个提交一次a ???
SQL语句应该怎么写啊?
表B(ID2,ID3)描述:我想批量更新表A的所有value字段, 如果ID1=ID2, 就把 id3更新到value字段
常规方法:
update a
set a.value= (select id3 from b where a.id1 = b.id2)
where exists (select 1 from b where a.id1 = b.id2 )但是表中的数据有几千万,,,到不过来的,,,请问能不能用循环更新1000个提交一次a ???
SQL语句应该怎么写啊?
declare
v_id number;
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select EXAM_RESULT_ITEM_ID,exam_result_item_id from the_d_exam_result_item;
begin
open cur_cursor;
loop
fetch cur_cursor into v_id,v_item_id;
if ( select count(1) from the_d_exam_result_frame where exam_result_item_id = v_frame_id))>0 then
select exam_result_frame_id into v_frame_id from the_d_exam_result_frame where exam_result_item_id = v_frame_id;
end if;
update the_d_exam_result_item set exam_result_id= v_frame_id where EXAM_RESULT_ITEM_ID = v_id;
commit;
exit hen cur_cursor%notfound;
end loop;
end;
请问怎么错了啊,,我ORACLE不太熟
你这样循环,效率比单个update语句更差的。
--加这句比较好,毕竟你不是所有的都需要更新
update a
set a.value= (select id3 from b where a.id1 = b.id2)
where exists (select 1 from b where a.id1 = b.id2 and a.value<>b.value)