本文共 3066 字,大约阅读时间需要 10 分钟。
需求:
有两张表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));