Question

oracle更新语句将1列值（字符串类型）全部加1.

ID为1，2，4，5，7 值为DF5811C4A0083，DF5811C4A0084，DF5811C4A0085，
DF5811C4A0086，DF5811C4A0087.

create table aa
(id number not null, vin varchar2(20) )

insert into aa values (1,'DF5811C4A0083');
insert into aa values (2,'DF5811C4A0084');
insert into aa values (4,'DF5811C4A0085');
insert into aa values (5,'DF5811C4A0086');
insert into aa values (7,'DF5811C4A0087');将对应的值变成DF5811C4A0084，DF5811C4A0085... 依次类推

Answer 1

update aa set vin=substr(vin,1,9)||to_char(lpad( to_char( to_number(substr (vin,10,4 ) +1 ) ) ,4,'0'))

Answer 2

update aa set vin=substr(vin,1,9)||lpad(to_number(substr(vin,-4))+1,4,'0')

追问

那再问下您，使用这个SQL语句会提示无效数字？？ 这个是为什么？
UPDATE RFIDMAPPING
SET ID = SUBSTR(ID, 1, 9) || LPAD(TO_NUMBER(SUBSTR(ID, -4)) + 1, 4, '0')
WHERE ID IS NOT NULL
AND ID >= (SELECT ID
FROM RFIDMAPPING
WHERE RFID = 'W104'
AND RFIDTYPE = 'W');

追答

你仔细检查一下字段类型吧

Answer 3

update aa set vin=substr(v_sin,1,9)||to_char(to_number(substr(vin,10,length(v_in) -10))+1);