如:有四张表,表1:id,a,b,c,d;表2:id,e,f,g;表3:id,h,i,j,k ;表四:sid,id,a,b,c,d,e,f ,g,h,i,j,k-----如何通过联合查询数据更新至一张表
select l. id,l.a,l.c,l.d,0 as e,0 as f, 0 as g, 0 as h, 0 as i, 0 as k from 表1 l
union all
select m.id,0 as a, 0 as b, 0 as c, m.e,m.f,m.g,0 as h,0 as i,0 as j, 0 as k from 表2 m
union all
select n.id, 0 as a, 0 as b, 0 as c, 0 as e, 0 as f, 0 as g ,n.h,n.i,n.j,n.k from 表3 n
请高手指定如何将查询数据更新至表4,必免重复更新数据!谢谢!
第一种方式:
insert into 表4
select …… from 表1
union
select …… from 表2
union
…………
select …… from 表n
如果觉得这样仍然有重复数据,可以考虑嵌套一下,加上distinct或者group by
例如:
insert into 表4
select distinct id,字段1,字段2……from (
select …… from 表1
union
select …… from 表2
union
…………
roych:谢谢你的回复,一如既往的强大;我想通过SQL作业完做到时时更新,完本打算插入数据不重复,又是多表更新至一张表单里,用merge into
using(select.....),
WHEN MATCHED THEN
update
set.....
when not matched then
insert ,结果是主键不能重复报错,
采用 inser into,需要设定条件不让它重复更新