问个update的语句
有表A
IDNAMEADDRESS
1IBMIBM_ADDR
2SONYSONY_ADDR
有表B
NAMEADDRESS
IBMNEW_IBM_ADDR
联想联想的地址
替换后表A结果为
IDNAMEADDRESS
1IBMNEW_IBM_ADDR
2SONYSONY_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 IBMNEW_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
