update test set id=to_char(to_number(id)+3070)) where to_number(id)>=13 and to_number(id)<=194;
写一个过程就行了declare sqlcmd varchar2(100); old_id number; new_id number; begin new_id :=3083; old_id:=13; FOR old_id IN 13..194 LOOP sqlcmd:='update test set id=‘||to_char(new_id)||' where id='||to_char(old_id); execute immediate sqlcmd; end loop; end;
如果列是字符型的: UPDATE TABNAME SET COL = TO_CHAR(TO_NUMBER(COL) + 3070) WHERE COL BETWEEN '13' AND '194'
update test set id=id+3070 where id between 13 and 194
UPDATE TABNAME SET COL = COL + 3070 WHERE COL BETWEEN 13 AND 194; 同意用这方法! 在oralce中,会自动转换数字进行加减!
sqlcmd varchar2(100);
old_id number;
new_id number;
begin
new_id :=3083;
old_id:=13;
FOR old_id IN 13..194 LOOP
sqlcmd:='update test set id=‘||to_char(new_id)||' where id='||to_char(old_id);
execute immediate sqlcmd;
end loop;
end;
UPDATE TABNAME SET COL = TO_CHAR(TO_NUMBER(COL) + 3070) WHERE COL BETWEEN '13' AND '194'
同意用这方法!
在oralce中,会自动转换数字进行加减!