如下两表
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中不同的信息
------解决方案--------------------
- SQL code
--> 测试数据：[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,nullupdate t2set [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.cnselect * from t2/*cn phone bm address ckphone ckbm ckaddressabc 111111 22223 abc123 111111 22222 123abc*/