把同步表:tbl_s1_m7_person_in_phase 中的数据更新基础表: tbl_s1_m5_person_info
但这样关联的两个字段必须为主键(有唯一性)
/*+ BYPASS_UJVC */的作用是跳过Oracle的键检查。 这样虽然能够执行了,但是如果pip_work_no中存在不唯一的数据,pi_work_no 就会被更新多次而导致意想不到的结果
update
(
select /*+ BYPASS_UJVC */
a.pip_name m_name,
b.pi_name n_name,
a.pip_sex m_sex,
b.pi_sex n_sex,
a.pip_state m_state,
b.pi_state n_state,
a.pip_kind m_kind,
b.pi_kind n_kind,
a.pip_adjust_post m_adjust_post,
b.pi_adjust_post n_adjust_post,
a.pip_enter_date m_enter_date,
b.pi_enter_date n_enter_date,
a.pip_is_subsidy m_subsidy,
b.pi_is_subsidy n_subsidy,
a.pip_identity_card m_identity_card,
b.pi_identity_card n_identity_card,
a.pip_birth_date m_birth_date,
b.pi_birth_date n_birth_date,
pkg_s1_m5_department.get_department_id_by_no(a.pip_department) m_department,
b.pi_di_id n_di_id,
a.pip_stature m_pip_stature,
b.pi_stature n_pip_stature,
b.pi_loader n_loader,
b.pi_load_date n_load_date,
b.pi_modifier n_modifier,
b.pi_modify_date n_modify_date,
b.pi_first_date n_first_date
from
tbl_s1_m7_person_in_phase a,
tbl_s1_m5_person_info b
where
a.pip_work_no = a_work_no
and b.pi_work_no = a_work_no
) t
set
t.n_name = t.m_name,
t.n_sex = t.m_sex,
t.n_state = t.m_state,
t.n_kind = t.m_kind,
t.n_adjust_post = t.m_adjust_post,
t.n_enter_date = t.m_enter_date,
t.n_subsidy = t.m_subsidy,
t.n_identity_card = t.m_identity_card,
t.n_birth_date = t.m_birth_date,
t.n_di_id = t.m_department,
t.n_pip_stature = decode(t.m_pip_stature, null, t.n_pip_stature, t.m_pip_stature),
t.n_loader = a_operator,
t.n_load_date = sysdate,
t.n_modifier = a_operator,
t.n_modify_date = sysdate,
t.n_first_date = t.m_enter_date;