oracle将一个表的数据更新到另一个表,用一个表中的数据更新到另一个表SQL语句(转)...

把同步表: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;