select a.PATENT,a.SOURCE,a.A1,a.A2,a.A3,a.A4 from table1 a
union all
select b.S1,b.S2,b.S3,b.S4 from table2 b
where a.patent=b.patent and a.source=b.source
union all
select b.S1,b.S2,b.S3,b.S4 from table2 b
where a.patent=b.patent and a.source=b.source
select a.patent,a.source,a1,a2,a3,a4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (b.patent is null or b.source is null)
union all
--表二有表一没
select b.patent,b.source,s1,s2,s3,s4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (a.patent is null or a.source is null)
union all
--都有,值取表一
select a.patent,a.source,a1,a2,a3,a4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (b.patent is not null and b.source is not null
and a.patent is not null and a.source is not null)
from
(
--表一有表二没的
select a.patent,a.source,a1,a2,a3,a4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (b.patent is null or b.source is null)
union all
--表二有表一没
select b.patent,b.source,s1,s2,s3,s4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (a.patent is null or a.source is null)
union all
--都有,值取表一
select a.patent,a.source,a1,a2,a3,a4
from table1 a,table2 b
where a.patent=b.patent and a.source=b.source
and (b.patent is not null and b.source is not null
and a.patent is not null and a.source is not null)
) c
select a.PATENT,a.SOURCE,a.A1,a.A2,a.A3,a.A4 ,b.S1,b.S2,b.S3,b.S4
from table1 as a,table2 as b
where a.patent=b.patent and a.source=b.source
UPDATE table3
SET table3.s1=table2.s1
FROM table1 INNER JOIN table2 ON titlestable1.=PATENT=table2.PATENT,table1.SOURCE=table2.SOURCE