有表A
ID NAME ADDRESS
1 IBM IBM_ADDR
2 SONY SONY_ADDR
有表B
NAME ADDRESS
IBM NEW_IBM_ADDR
联想 联想的地址
替换后表A结果为
ID NAME ADDRESS
1 IBM NEW_IBM_ADDR
2 SONY SONY_ADDR
如何不把表B里的数据取到变量里再用update替换,而是直接用update语句替换
------解决方案--------------------
update a
set a.ADDRESS = b.ADDRESS
from 表A a
join 表B b on a.name = b.name
------解决方案--------------------
update A
set A.ADDRESS=B.ADDRESS
from A inner join B on A.NAME=B.NAME
------解决方案--------------------
create table 表A(
ID int identity(1,1),
NAME varchar(20),
ADDRESS nvarchar(20)
)
insert 表A
select 'IBM ', 'IBM_ADDR ' union all
select 'SONY ', 'SONY_ADDR '
create table 表B(
NAME varchar(20),
ADDRESS nvarchar(20)
)
insert 表B
select 'IBM ', 'NEW_IBM_ADDR ' union all
select 'SONY ', '联想的地址 '
select * from 表A
select * from 表B
update a
set a.ADDRESS=b.ADDRESS
from 表A a inner join 表B b on a.NAME=b.NAME
--result:
ID NAME ADDRESS
---------------------------------
1 IBM NEW_IBM_ADDR
2 SONY 联想的地址
------解决方案--------------------
update a
set a.address=newb.address
from
(Select B.Name,B.Address From A,B
Where A.Name=B.Name And A.Address <> B.Address) newb
Where A.Name=Newb.Name