高分求SQL语句

[liuchunjp]

表A 有个子段名为 Model_Name , 现要把这个字段更新。条件是，此字段第5位的字符为 A 的话 把A 设为 1 ，如果为B 的话 设为 2 。
例子：ModelA 更新后为 Model1
ModelB 更新后为 Model2

[cenlmmx]

INSTR、SUBSTR 和REPLACE

[liuchunjp]

要详细的，马上可以用的。

[swimming8243]

update table set
Model_Name = Substr(Model_Name, 1, 4)||decode(Substr(Model_Name, 5, 1),'A','1','B','2')
||Substr(Model_Name, 6, length(Model_Name))

[ljgeneral317]

declare
cursor cur_for_update is select a.serialno/*A表主键*/,a.Model_Name from A a
v_model_name varchar2(100);
v_serialno number(10);
begin
open cur_for_update;

loop
fetch cur_for_update into v_serialno,v_model_name ;exit when cur_for_update%notfound;
v_model_name:=replace(v_model_name,'ModelA ','Model1');
v_model_name:=replace(v_model_name,'ModelB ','Model2');
---如果没有特殊之可以
---v_model_name:=translate(v_model_name,'ABCDEFG','1234567');
update A set Model_Name=v_model_name where serialno=v_serialno
end loop;
close cur_for_update;
end;

[客家族_Shark曾_小凡仙]

update A
set Model_Name = Substr(Model_Name, 1, 4)||decode(Substr(Model_Name, 5, 1),'A','1','B','2')||Substr(Model_Name, 6, length(Model_Name))

[liuchunjp]

马上给分，谢

[wangzk0206]

update a a1 set model_name=(select rn from (
select rowid rid,substr(model_name,1,4)||decode(substr(model_name,5,1),'a','1','b','2')||substr
el_name,6) rn
from a ) a2
where a1.rowid=a2.rid )
/

新3行。

select * from a
/

ID MODEL_NAME
------ ----------
1 mode1
2 mode2
3 mode1ble

[boydgmx]

update A
set Model_Name = Substr(Model_Name, 1, 4)
||decode(Substr(Model_Name, 5, 1),'A','1','B','2')
||Substr(Model_Name, 6);