修改关联到两个表，求救

Question

有两个表personal_pat_master_index 和personal_pat_master_index1 两个表中关联字段是身份证， 我要把两个表中身份证相同的 personal_pat_master_index的数据，修改为personal_pat_master_index1的数据,我是这样写的update personal_pat_master_index p,community.personal_pat_master_index1 p1
   set p.work_units         = p1.work_units,
       p.phoneself          = p1.phoneself,
       p.next_of_kin        = p1.next_of_kin,
       p.next_of_kin_phone  = p1.next_of_kin_phone,
       p.usualtype          = p1.usualtype,
       p.nation             = p1.nation,
where p.id_no = p1.id_no
   and p1.community_Code = '431028001' and p1.community_code=p.community_code说缺失SET关键 在update personal_pat_master_index p,community.personal_pat_master_index1 p1
逗号这

Answer 1

update personal_pat_master_index p
set (p.work_units,
    p.phoneself,
    p.next_of_kin,
    p.next_of_kin_phone,
    p.usualtype,
    p.nation
    )=
    (
    select p1.work_units,
           p1.phoneself,
           p1.next_of_kin,
           p1.next_of_kin_phone,
           p1.usualtype,
           p1.nation
    from community.personal_pat_master_index1 p1
    where p.id_no = p1.id_no
          and p1.community_Code = '431028001' and 
          p1.community_code=p.community_code
    )

Answer 2

--改正了一下
update personal_pat_master_index p
set (p.work_units,
    p.phoneself,
    p.next_of_kin,
    p.next_of_kin_phone,
    p.usualtype,
    p.nation)
    =
    (
    select p1.work_units,
           p1.phoneself,
           p1.next_of_kin,
           p1.next_of_kin_phone,
           p1.usualtype,
           p1.nation
    from personal_pat_master_index1 p1
    where p.id_no = p1.id_no and 
          p1.community_Code = '431028001' and 
          p1.community_code=p.community_code
    )
where exits(
      select p1.work_units,
           p1.phoneself,
           p1.next_of_kin,
           p1.next_of_kin_phone,
           p1.usualtype,
           p1.nation
      from personal_pat_master_index1 p1
      where p.id_no = p1.id_no)

Answer 3

上面那个不可以吗？
试试这个
update personal_pat_master_index
set       (work_units,
           phoneself,
           next_of_kin,
           next_of_kin_phone,
           usualtype,
           nation)=(
    select work_units,
           phoneself,
           next_of_kin,
           next_of_kin_phone,
           usualtype,
           nation
    from personal_pat_master_index1)
where exits(
      select 1
      from personal_pat_master_index p,
           personal_pat_master_index1 p1
      where p.id_no = p1.id_no)

Answer 4

MARK吧，怎么感觉都有一些问题的说。

Answer 5

我更新两个关联表一般用这种方式。===================================================================
merge into personal_pat_master_index p
using community.personal_pat_master_index1 p1
on (p.id_no = p1.id_no)
when matched then 
  update set p.work_units = p1.work_units,
  p.phoneself = p1.phoneself,
  p.next_of_kin = p1.next_of_kin,
  p.next_of_kin_phone = p1.next_of_kin_phone,
  p.usualtype = p1.usualtype,
  p.nation = p1.nation================================================

Answer 6

--如楼上兄台说的，更新关联表，常用oracle 9i引入的merge语句：
merge into personal_pat_master_index p
using personal_pat_master_index1 p1
on (p.id_no=p1.id_no)
when matched then
update
set  p.work_units = p1.work_units,
     p.phoneself = p1.phoneself,
     p.next_of_kin = p1.next_of_kin,
     p.next_of_kin_phone = p1.next_of_kin_phone,
     p.usualtype = p1.usualtype,
     p.nation = p1.nation;
--再说，测试怎么能用正式库呢？
--另外创建两个表，如实验成功，再确定更新