表一:aa 表二:aa2 表一
id tel nr id tel nr id tel nr
1 2356412 ... 1 2356412 aaa 效果见右表: 1 2356412 aaa
2 5342154 ... 2 6874520 bbb 4 6874520 bbb
3 8654152 ...
4 6874520 ...
表一与表二是主从表,表一为主表,表二的tel在表一中都能找到.
现在我想实现的是把表二tel字段对应的nr复制到表一tel对应的nr?
id tel nr id tel nr id tel nr
1 2356412 ... 1 2356412 aaa 效果见右表: 1 2356412 aaa
2 5342154 ... 2 6874520 bbb 4 6874520 bbb
3 8654152 ...
4 6874520 ...
表一与表二是主从表,表一为主表,表二的tel在表一中都能找到.
现在我想实现的是把表二tel字段对应的nr复制到表一tel对应的nr?
if object_id('tempdb.dbo.#aa') is not null drop table #aa
create table #aa (id int,tel int,nr varchar(3))
insert into #aa
select 1,2356412,null union all
select 2,5342154,null union all
select 3,8654152,null union all
select 4,6874520,null
--> 测试数据: #aa2
if object_id('tempdb.dbo.#aa2') is not null drop table #aa2
create table #aa2 (id int,tel int,nr varchar(3))
insert into #aa2
select 1,2356412,'aaa' union all
select 2,6874520,'bbb'update a set a.nr=b.nr from #aa a join #aa2 b on a.tel=b.telselect * from #aa/*
id tel nr
----------- ----------- ----
1 2356412 aaa
2 5342154 NULL
3 8654152 NULL
4 6874520 bbb
*/
from aa
inner join aa2 on aa.tel=aa2.tel