首先祝大家新年快乐,在娱乐之余可以看看这个问题。
有两张表,表结构一致,如下:
create table test1
(
id int,
name varchar(100)
)
create table test2
(
id int,
name varchar(100)
)其中id是主键
表中数据如下
test1
id name
1 a
2 btest2
id name
1 c
2 d目的是将表test1中的name字段信息更新为test2表中主键一致的name信息
test1
id name
1 c
2 d请问用sql写,不遍历,不使用游标如何做,sql语句越简单越好
有两张表,表结构一致,如下:
create table test1
(
id int,
name varchar(100)
)
create table test2
(
id int,
name varchar(100)
)其中id是主键
表中数据如下
test1
id name
1 a
2 btest2
id name
1 c
2 d目的是将表test1中的name字段信息更新为test2表中主键一致的name信息
test1
id name
1 c
2 d请问用sql写,不遍历,不使用游标如何做,sql语句越简单越好
from test1 a,test2 b where a.id=b.id
(
id int,
name varchar(100)
)
create table test2
(
id int,
name varchar(100)
)
insert into test1 (id,name)
select 1,'a'
union all
select 2,'b'
insert into test2 (id,name)
select 1,'c'
union all
select 2,'d'--你要求的SQL语句
update test1 set name=b.name
from test1 as a
inner join test2 as b on a.id=b.id
select * from test1drop table test1,test2
update test1 set name=b.name
from test1 as a
inner join test2 as b on a.id=b.id
set Name=isnull((select top 1 Name from test2 where ID=a.ID),Name)
from test1 a
name = b.name
from test1 a
join test2 b on a.id=b.id
set name=(select min(name) from test2 where id=test1.id)
where exists (select name from test2 where id=test1.id)
update test1 set a.name=b.name
from test1 a,test2 b where a.id=b.id
from test1 as a
inner join test2 as b on a.id=b.id
where a.id=b.id