update pha_com_baseinfo a
set a.drug_code = (select (lpad(rownum + 70000, 11, '0'))
from (select b.drug_type drug_type,
b.old_drug_code
from pha_com_baseinfo b
order by b.drug_type) b
where a.old_drug_code = b.old_drug_code)
将old_drug_code的值重新修改,现在的问题是update后的值多是一样的,都是000000070001的编码。实际update后想得到的值是000000070001
000000070002
000000070003
.
.
.
.
set a.drug_code = (select (lpad(rownum + 70000, 11, '0'))
from (select b.drug_type drug_type,
b.old_drug_code
from pha_com_baseinfo b
order by b.drug_type) b
where a.old_drug_code = b.old_drug_code)
将old_drug_code的值重新修改,现在的问题是update后的值多是一样的,都是000000070001的编码。实际update后想得到的值是000000070001
000000070002
000000070003
.
.
.
.
b.old_drug_code
from pha_com_baseinfo b
order by b.drug_type需要对b.drug_type排序后,在生成新的drug_code
UPDATE pha_com_baseinfo a
SET a.drug_code =
(SELECT rn
FROM (SELECT (lpad(rownum + 70000, 11, '0')) rn,
drug_type,
old_drug_code
FROM (SELECT a.drug_type drug_type, a.old_drug_code
FROM pha_com_baseinfo a
ORDER BY b.drug_type)) b
WHERE a.old_drug_code = b.old_drug_code);
UPDATE pha_com_baseinfo a
SET a.drug_code =
(SELECT rn
FROM (SELECT (lpad(rownum + 70000, 11, '0')) rn,
drug_type,
old_drug_code
FROM (SELECT a.drug_type drug_type, a.old_drug_code
FROM pha_com_baseinfo a
ORDER BY a.drug_type)) b
WHERE a.old_drug_code = b.old_drug_code);
因此,你需要将全部记录先排序(1层子查询),再取编号(2层子查询),再关联update(3层子查询)