update b set newname = a.name from b,a where b.id = a.id
--或者 update b set newname = (select name from a where id = b.id)
update b set newname = a.name from b left outer join a on b.id = a.id
update a as a2 left join b as b2 on a2.id=b2.id 这样LEFT JOIN 可不可以?
你只是替换ID相同的话,不需要left join
还是高手说得对update b set newname = a.name from a inner join b on a.id = b.id
update b set b.newname=a.name from b,a where b.id=a.id或update b set b.newname=a.name from b right outer join a on a.id=b.id都可以实现。但update b set newname=(select name from a where id=b.id)会将b表newname原来的值改为null
/* A表有两个字段,ID和NAME,B表有ID和NEWname,ID两表对应和唯一,如果A表ID在B表内也有,那么更新B表的NEWNAME为A表的name */update A set NAME=B.NAME from A join B on A.ID=b.ID
--不能用 LEFT JOIN 否则会将B表中没有对应 ID 的数据更新为 NULLdeclare @A table (ID int, NAME varchar(1)) insert @A select 1,'a' insert @A select 2,'b'declare @B table (ID int, NAME varchar(1)) insert @B select 2,'C' insert @B select 3,'D'-- join update @A set NAME=B.NAME from @A A join @B B on A.ID=b.ID select * from @A /* ID NAME ----------- ---- 1 a 2 C */-- left join update @A set NAME=B.NAME from @A A left join @B B on A.ID=b.ID select * from @A /* ID NAME ----------- ---- 1 NULL 2 C */
update b set newname = a.name from b,a where b.id = a.id这一段在SQL 2005里不能用了,自动会转换为 update b set newname = a.name from a inner join b on a.id = b.id
set newname = a.name
from b,a
where b.id = a.id
update b
set newname = (select name from a where id = b.id)
set newname = a.name
from b
left outer join a on
b.id = a.id
这样LEFT JOIN 可不可以?
newname = a.name
from a
inner join b on
a.id = b.id
from b,a
where b.id=a.id或update b set b.newname=a.name
from b right outer join a on a.id=b.id都可以实现。但update b set newname=(select name from a where id=b.id)会将b表newname原来的值改为null
A表有两个字段,ID和NAME,B表有ID和NEWname,ID两表对应和唯一,如果A表ID在B表内也有,那么更新B表的NEWNAME为A表的name
*/update A set NAME=B.NAME from A join B on A.ID=b.ID
insert @A select 1,'a'
insert @A select 2,'b'declare @B table (ID int, NAME varchar(1))
insert @B select 2,'C'
insert @B select 3,'D'-- join
update @A set NAME=B.NAME from @A A join @B B on A.ID=b.ID
select * from @A
/*
ID NAME
----------- ----
1 a
2 C
*/-- left join
update @A set NAME=B.NAME from @A A left join @B B on A.ID=b.ID
select * from @A
/*
ID NAME
----------- ----
1 NULL
2 C
*/
update b
set newname = a.name
from b,a
where b.id = a.id这一段在SQL 2005里不能用了,自动会转换为
update b set
newname = a.name
from a
inner join b on
a.id = b.id