有这样两张表
表1
ID Name
-----------------
1
2
3
4
5
6
7
8
表2
Name
-----------------
AAAA
BBBB
CCCC
DDDD
EEEE
FFFF
GGGG
HHHH
表1有两个字段，Name字段为空，而表2只有一个Name字段没有对应的ID。问题我想把表2中Name值插入到表1中去，请教这条SQL如何写？
------解决方案--------------------
update tb1 set name = n.name from m , ((select count(1) from t2 where name < t.name) + 1 id , name from t2 t) n where m.id = n.id
------解决方案--------------------
/*
ID Name
-----------------
1
2
3
4
5
6
7
8
表2
Name
-----------------
AAAA
BBBB
CCCC
DDDD
EEEE
FFFF
GGGG
HHHH
*/
go
if OBJECT_ID('A')is not null
drop table A
go
create table A(
ID int,
Name varchar(4)
)
go
insert A
select 1,'' union all
select 2,'' union all
select 3,'' union all
select 4,'' union all
select 5,'' union all
select 6,'' union all
select 7,'' union all
select 8,''
go
if OBJECT_ID('B')is not null
drop table B
go
create table B(
Name varchar(4)
)
go
insert B
select 'AAAA' union all
select 'BBBB' union all
select 'CCCC' union all
select 'DDDD' union all
select 'EEEE' union all
select 'FFFF' union all
select 'GGGG' union all
select 'HHHH'
update A set Name=m.Name from (select ROW_NUMBER()over(order by getdate()) as num,* from B)m
where A.ID=M.num
select *from A
/*
ID Name
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG
8 HHHH
*/