需求:A 有2个字段,project_code,project_name。B有2个字段,project_code,project_name。其中A的project_name 在B都有且B中project_name有对应的project_code,而A却有部分数据有项目名称但没编码,所以现在需要根据B的数据来更新A。
首先,根据
select t.project_code, t.project_name
from exec_section_index t, bdg_prj_relate t1
where t.project_code is null
and t.project_name is not null
and t.project_name = t1.project_name;
能得到需要的字段 project_code,project_name.
然后组装更新A的Update语句。
select 'update exec_section_index t set t.project_code =<' ||
t1.project_code || '< where t.project_name =<' || t1.project_name || '<;'
from exec_section_index t, bdg_prj_relate t1
where t.project_code is null
and t.project_name is not null
and t.project_name = t1.project_name;
最后得到'update exec_section_index t set t.project_code =<xxx> where t.project_name =<'xxx>;
替换‘<’为‘'’即可得能执行的脚本。