用一个表中的数据更新到另一个表SQL语句-CSDN博客

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