如下两表
t1:
cn phone bm address
t2:
cn phone bm address ckphone ckbm ckaddress
条件是根据t1和t2的cn来比对,也就是查找两表cn项相同,但是其他字段不同的数据,并在t2表中的ck开头的字段标出来不同的t1的相应字段信息
例如
t1:
cn phone bm address
abc 111111 22222 123abc
t2:
cn phone bm address ckphone ckbm ckaddress
abc 111111 22223 abc123
那么比对t2表更新成如下形式:
cn phone bm address ckphone ckbm ckaddress
abc 111111 22223 abc123 22222 123abc
这排版无语了,上面这个phone字段一样,所以ckphone是空的,ckbm和ckaddress是更新成t1中不同的信息
--> 测试数据：[t1]
if object_id('[t1]') is not null
drop table [t1]
create table [t1](
[cn] varchar(3),
[phone] int,
[bm] int,
[address] varchar(6)
)
insert [t1]
select 'abc',111111,22222,'123abc'
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2](
[cn] varchar(3),
[phone] int,
[bm] int,
[address] varchar(6),
[ckphone] sql_variant,
[ckbm] sql_variant,
[ckaddress] sql_variant
)
insert [t2]
select 'abc',111111,22223,'abc123',null,null,null
update t2
set [ckphone]=t.phone,[ckbm]=t.bm,[ckaddress]=t.[address]
from(
select * from t1 a where not exists(
select 1 from t2 b where a.cn=b.cn and a.phone<>b.phone
))t where t.cn=t2.cn
select * from t2
/*
cn phone bm address ckphone ckbm ckaddress
abc 111111 22223 abc123 111111 22222 123abc
*/