有这样两张表表1 ID Name
-----------------
1
2
3
4
5
6
7
8表2Name
-----------------
AAAA
BBBB
CCCC
DDDD
EEEE
FFFF
GGGG
HHHH表1有两个字段,Name字段为空,而表2只有一个Name字段没有对应的ID。问题我想把表2中Name值插入到表1中去,请教这条SQL如何写?
-----------------
1
2
3
4
5
6
7
8表2Name
-----------------
AAAA
BBBB
CCCC
DDDD
EEEE
FFFF
GGGG
HHHH表1有两个字段,Name字段为空,而表2只有一个Name字段没有对应的ID。问题我想把表2中Name值插入到表1中去,请教这条SQL如何写?
解决方案 »
- 用SQL动态创建表如何做?
- 我知道另一台服务器上的sa密码,有办法在我的机器上的sqlserver里用sql语句对这台服务器操作吗?不用注册的方法
- 我的SQL的企业管理器里为什么看不到数据库了?
- 请多多指教
- SQL Server2005会不会拖垮服务器?
- SQL语句求有两个以,分隔的字符串的交集
- Errors During Transaction Processing 这段文字不是自相矛盾吗?
- 【江湖告急】为什么我的数据库运行之后比预期的大了10倍?
- 要求用户编号为字母、数字和下划线的组合,详情请进……
- 查询,特急!在线等待!PLEASE HELP ME!
- 关于sql server 中的排序规则的疑问
- 值得下大功夫看sql server2000的联机丛书吗?
create table t1(ID int, Name varchar(10))
insert into t1 values( 1,'')
insert into t1 values( 2,'')
insert into t1 values( 3,'')
insert into t1 values( 4,'')
insert into t1 values( 5,'')
insert into t1 values( 6,'')
insert into t1 values( 7,'')
insert into t1 values( 8,'')
create table t2(Name varchar(10))
insert into t2 values('AAAA')
insert into t2 values('BBBB')
insert into t2 values('CCCC')
insert into t2 values('DDDD')
insert into t2 values('EEEE')
insert into t2 values('FFFF')
insert into t2 values('GGGG')
insert into t2 values('HHHH')
goupdate t1 set name = n.name from t1 m , (select (select count(1) from t2 where name < t.name) + 1 id , name from t2 t) n where m.id = n.id select * from t1drop table t1 , t2/*
ID Name
----------- ----------
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG
8 HHHH(所影响的行数为 8 行)*/--如果t1表的ID不是顺序的,可如下操作:
create table t1(ID int, Name varchar(10))
insert into t1 values( 1,'')
insert into t1 values( 2,'')
insert into t1 values( 3,'')
insert into t1 values( 4,'')
insert into t1 values( 5,'')
insert into t1 values( 6,'')
insert into t1 values( 7,'')
insert into t1 values( 8,'')
create table t2(Name varchar(10))
insert into t2 values('AAAA')
insert into t2 values('BBBB')
insert into t2 values('CCCC')
insert into t2 values('DDDD')
insert into t2 values('EEEE')
insert into t2 values('FFFF')
insert into t2 values('GGGG')
insert into t2 values('HHHH')
goupdate t1 set name = n.name from t1 m ,
(select (select count(1) from t1 where id < t.id) + 1 px , id from t1 t) o,
(select (select count(1) from t2 where name < t.name) + 1 px , name from t2 t) n
where m.id = o.id and o.px = n.pxselect * from t1drop table t1 , t2/*
ID Name
----------- ----------
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG
8 HHHH(所影响的行数为 8 行)*/
create table t1(ID int, Name varchar(10))
insert into t1 values( 1,'')
insert into t1 values( 2,'')
insert into t1 values( 3,'')
insert into t1 values( 4,'')
insert into t1 values( 5,'')
insert into t1 values( 6,'')
insert into t1 values( 7,'')
insert into t1 values( 8,'')
create table t2(Name varchar(10))
insert into t2 values('AAAA')
insert into t2 values('BBBB')
insert into t2 values('CCCC')
insert into t2 values('DDDD')
insert into t2 values('EEEE')
insert into t2 values('FFFF')
insert into t2 values('GGGG')
insert into t2 values('HHHH')
goupdate t1 set name = n.name from t1 m , (select row_number() over(order by name) id , name from t2 t) n where m.id = n.id select * from t1drop table t1 , t2/*
ID Name
----------- ----------
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG
8 HHHH(8 行受影响)
*/--如果t1表的ID不是顺序的,可如下操作:
create table t1(ID int, Name varchar(10))
insert into t1 values( 1,'')
insert into t1 values( 2,'')
insert into t1 values( 3,'')
insert into t1 values( 4,'')
insert into t1 values( 5,'')
insert into t1 values( 6,'')
insert into t1 values( 7,'')
insert into t1 values( 8,'')
create table t2(Name varchar(10))
insert into t2 values('AAAA')
insert into t2 values('BBBB')
insert into t2 values('CCCC')
insert into t2 values('DDDD')
insert into t2 values('EEEE')
insert into t2 values('FFFF')
insert into t2 values('GGGG')
insert into t2 values('HHHH')
goupdate t1 set name = n.name from t1 m ,
(select row_number() over(order by id) px , id from t1 t) o,
(select row_number() over(order by name) px , name from t2 t) n
where m.id = o.id and o.px = n.pxselect * from t1drop table t1 , t2/*
ID Name
----------- ----------
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG
8 HHHH(8 行受影响)
*/
ID Name
-----------------
1
2
3
4
5
6
7
8表2Name
-----------------
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
*/