oracle 实现 关联两个表更新 update select

需求：

       有两张表A,B。表A和表B有共同的字段，id，dev_ip，collector_id. 其中id是作为两个表关联的唯一标识。即表A中的id与表B中的id是一一对应的。本来B表中的字段collector_id应该是完全一致的，但是由于某些原因B表中的collect_id值被修改了，现在需要将A,B表的collector_id同步成一致。即将A表中与B表id和dev_ip一样的数据行更新B表中的collector_id为A表中的collector_id值。

 

实现方法有以下几种:

tb_device_info表为B表，tb_device表为A表

方法一：

    update tb_device_info di set collectorid = (select d.collector_id from tb_device d where d.id = di.deviceid )  
where deviceid =  (select d.id from  tb_device d where d.id = di.deviceid and di.collectorid <> d.collector_id)

 

方法二：道理同方法一。

  

    update tb_device_info di set collectorid = (select d.collector_id from tb_device d where d.id = di.deviceid )  
where （select 1  from  tb_device d where d.id = di.deviceid and di.collectorid <> d.collector_id)

 

方法三：原理是更新已查条件结果集，相当于更新同一个表中的两个不同字段。

    update (select di.deviceid ,di.dev_ip ,di.collectorid ,d.id ,d.dev_ip ,d.collector_id 
from tb_device_info di ,tb_device d where di.deviceid = d.id and di.collectorid <> d.collector_id)
 set collectorid= collector_id 

 

 方法四：存储过程的方式

declare  cursor cur_collecot 
is 
select collector_id,id from tb_device;   
  begin        
    for my_cur in cur_collecot loop   
          update tb_devic_info set collectorid  = my_cur.collector_id  
               where deviceid = my_cur.id;        
end loop; 

 

以上方法参考网络文章终结，记录以备使用

另转载网络牛人原文

 

 

为了做分析，需要整合一些数据到一个表中，涉及到通过主键关联多个表，获取其中的某些字段的值，通过update可以简单实现，找了一篇文章，挺不错：

    批量更新表时，update一个表的列时，需要依赖另外的表，这种依赖可以是where条件子句，也可以要update的field的值依赖另外的表
   通常有两种做法
   1.使用存储过程
   2.在程序代码里逐条循环执行
这里给出一种更高效、简洁的做法，批量更新SQL ，一句SQL就可以替代麻烦的循环过程，有MS SQLServer、Oracle、DB2下的写法
--关键点：t4和t1是同一个table，primary key肯定也是同一个，
--并以它进行关联，这样在 select语句里即可引用到要update的表的fields 
UPDATE Table1 AS t1 
SET (Field1,Field2) = (SELECT Field21, Field22 
                       FROM Table2 t2 
                            INNER JOIN Table3 t3 
                                 ON t3.Field31 = t2.Field23 
                            INNER JOIN Table4 t4 
                                 ON t3.Field32 = t4.Filed41 
                       WHERE t2.Field24 >= '' 
                                      AND t1.fId = t4.fId); 
----------------------------MS SQLServer --------------------------------------

UPDATE t1 
SET Field1 = Field21, Field2 = Field22 
FROM Table2 t2 
         INNER JOIN Table3 t3 
              ON t3.Field31 = t2.Field23 
         INNER JOIN Table4 t4 
              ON t3.Field32 = t4.Filed41 
WHERE ((t2.Field24 >= '') 
                  AND t1.fId = t4.fId); 
----------------------------Oracle --------------------------------------------

UPDATE Table1 t1 
SET (Field1,Field2) = (SELECT Field21, Field22 
                       FROM Table2 t2 
                            INNER JOIN Table3 t3 
                                 ON t3.Field31 = t2.Field23 
                            INNER JOIN Table4 t4 
                                 ON t3.Field32 = t4.Filed41 
                       WHERE ((t2.Field24 >= '') 
                                 AND t1.fId = t4.fId)) 
WHERE EXISTS (SELECT Field21, Field22 
              FROM Table2 t2 
                   INNER JOIN Table3 t3 
                        ON t3.Field31 = t2.Field23 
                   INNER JOIN Table4 t4 
                        ON t3.Field32 = t4.Filed41 
              WHERE ((t2.Field24 >= '') 
                               AND t1.fId = t4.fId)); 
---------------------------------DB2 ------------------------------------------

UPDATE Table1 AS t1 
SET (Field1,Field2) = (SELECT Field21, Field22 
                       FROM Table2 t2 
                            INNER JOIN Table3 t3 
                                 ON t3.Field31 = t2.Field23 
                            INNER JOIN Table4 t4 
                                 ON t3.Field32 = t4.Filed41 
                       WHERE ((t2.Field24 >= '') 
                                         AND t1.fId = t4.fId)) 
WHERE EXISTS (SELECT Field21, Field22 
              FROM Table2 t2 
                   INNER JOIN Table3 t3 
                        ON t3.Field31 = t2.Field23 
                   INNER JOIN Table4 t4 
                        ON t3.Field32 = t4.Filed41 
              WHERE ((t2.Field24 >= '') 
                                AND t1.fId = t4.fId));