SQL 两个表根据同一标识更改次表信息

表结构如下:
create table tab1(
 id int identity,
 tname varchar(50)
 )

create table tab2
(
idd varchar(10),
iname varchar(50)
)


insert into tab1(tname ) //表1数据                  
select 'a0'
union all select 'b1'
union all select 'b2'
union all select 'b3'
union all select 'b4'
union all select 'b5'
union all select 'b6'
union all select 'b7'


insert into tab2(iname)
 //表2数据              
 select 'a0'
union all select 'b1'
union all select 'b2'
union all select 'b3'
union all select 'b4'
union all select 'b5'
union all select 'b6'
union all select 'b7'


表一与表二有个共同点,就是tname和iname一样,从而我的需求为,将表一中的id取到表2中对应iname相同值的,从而更新表2中的值

解决方法两种:


1:
update tab2 set tab2.idd = tab1.id from tab2 left join tab1 on tab2.iname=tab1.tname



2:
 declare @tab_Name varchar(20)
 declare @tab_Name1 varchar(20)

declare @tab_Name2 varchar(20)
declare @tab_Name3 varchar(20)
declare @tab1 table (sid int identity ,tid varchar(20),tname varchar(20))
declare @tcount int

set @tab_Name1='USE_0088_0006'

insert into @tab1 select 所属支部, 英文缩写 from [人力资源-人员-基本情况]



set @tcount =1
 while @tcount<1133
   begin
      select  @tab_Name2=tid,@tab_Name3=tname from @tab1 where sid=@tcount

       update USE_0087 set  USE_0087_0048=@tab_Name2 where USE_0087_0049=@tab_Name3
       set @tcount =@tcount+1
   end
   select * from USE_0087
方法还可以优化
相关文章
相关标签/搜索