日期:2014-05-18 浏览次数:20594 次
--有TAB1 BH NAME ID1 ID2 1 NAME1 1001 20001 2 NAME2 1002 20002 3 NAME3 1003 20003 4 NAME4 1004 20004 5 NAME5 1005 20005 ..... --TAB2 XH NAME ID1 ID2 1 NAME1 20001 2 NAME1 20001 2 NAME2 20002 3 NAME3 20003 4 NAME4 20004 5 NAME5 20005 ..... --通过ID2关联,得到TAB2 XH NAME ID1 ID2 1 NAME1 1001 20001 2 NAME1 1001 20001 2 NAME2 1002 20002 3 NAME3 1003 20003 4 NAME4 1004 20004 5 NAME5 1005 20005 .....
--> 测试数据:[TAB1] if object_id('[TAB1]') is not null drop table [TAB1] create table [TAB1]( [BH] int, [NAME] varchar(5), [ID1] int, [ID2] int ) insert [TAB1] select 1,'NAME1',1001,20001 union all select 2,'NAME2',1002,20002 union all select 3,'NAME3',1003,20003 union all select 4,'NAME4',1004,20004 union all select 5,'NAME5',1005,20005 --> 测试数据:[TAB2] if object_id('[TAB2]') is not null drop table [TAB2] create table [TAB2]( [XH] int, [NAME] varchar(5), [ID1] int, [ID2] int ) insert [TAB2] select 1,'NAME1',null,20001 union all select 2,'NAME1',null,20001 union all select 2,'NAME2',null,20002 union all select 3,'NAME3',null,20003 union all select 4,'NAME4',null,20004 union all select 5,'NAME5',null,20005 update [TAB2] set [ID1]=a.[ID1] from [TAB1] a where a.NAME=[TAB2].NAME and a.ID2=[TAB2].ID2 select * from [TAB2] /* XH NAME ID1 ID2 1 NAME1 1001 20001 2 NAME1 1001 20001 2 NAME2 1002 20002 3 NAME3 1003 20003 4 NAME4 1004 20004 5 NAME5 1005 20005 */