在使用implict update table 时,发现其中一个表一定要有唯一约束,否则会报错!但是oracle可以使用hints:/*+ BYPASS_UJVC*/ 屏蔽掉队唯一性的检查。
update (select /*+ BYPASS_UJVC */
*
from t_contract_template ct,
(select distinct (t.id) vid,
t.serial_number || '-' ||
substr(pkg_map.org_company2segment(c.companyid),
length(pkg_map.org_company2segment(c.companyid)) - 1,
2) as vnum
FROM t_contract_template t,
t_contract_template_ver v,
t_template_city c
where t.id = v.template_id
and t.id = c.templateverid
and v.enforce = 7
and t.serial_number is not null
and INSTR(t.serial_number, '-') = 0
and length(t.serial_number) = 10) vv
where ct.id = vv.vid) tct
set tct.serial_number = tct.vnum;
使用上面的方法,我们可以将查询出的结果集直接做更新,这样我们可以解决一下复杂表结构无法更新的问题了.