使用表连接Update和delete

update /* nologging */
          (select /*+ leading(m) use_hash(c, m) */  
                  m.sidea_port_id           m_ap_id,
                  m.sidea_port_spec_id      m_aps_id,
                  m.sideb_port_id           m_bp_id,
                  m.sideb_port_spec_id      m_bps_id,
                  c.sidea_port_id           c_ap_id,
                  c.sidea_port_spec_id      c_aps_id,
                  c.sideb_port_id           c_bp_id,
                  c.sideb_port_spec_id      c_bps_id
             from mid_user_line_relation_ex m,
                  rm.connector              c
            where m.device_id      = c.device_id
              and m.connector_code = c.code)
      set m_ap_id = c_ap_id, m_aps_id = c_aps_id, m_bp_id = c_bp_id, m_bps_id = c_bps_id;

ERROR 位于第 15 行:
ORA-01779: 无法修改与非键值保存表对应的列



解决方法：解决方法就是保证数据源的唯一性，例如本例中可以为connector的device_id，code创建一个唯一索引：

CREATE UNIQUE INDEX uidx_connector_device_code on connector(device_id, code);

参考：http://rainbowbridg.itpub.net/post/23663/295750


表连接的DELETE
表连接delete


SQL> select *  from test;

         A          B          C D
---------- ---------- ---------- --------------------
         1          1          1 1
         2          2          2 2
         3          3          3 wew

SQL> select * from test1;

未选定行

已用时间:  00: 00: 00.00
SQL> insert into test1 values(1,9);

已创建 1 行。

SQL> delete (select a.a aa,a.b ab,a.c ac,a.d ad from test a,test1 b where a.a = b.a );

已删除 1 行。
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> select  *from test;

         A          B          C D
---------- ---------- ---------- --------------------
         2          2          2 2
         3          3          3 wew

已用时间:  00: 00: 00.00
SQL> select * from test1;

         A B
---------- --------------------
         1 9

已用时间:  00: 00: 00.00
SQL>


参考：http://www.itpub.net/viewthread.php?tid=45822&extra=&page=1