在线等，求sql

[love_study]

A表
字段 pageid pagename 其中 pageid 是唯一的 pagename 里没有内容 比如

pageid pagename
1
2
3
4

B表

pageid pagename pageid 和 pagename 都不唯一 比如

pageid pagename
1
1 标题二
2 标题辞1
2 标题 2
3 标题3
4 标题4

我现在想把A表里的 pagename 填上内容只要不为容就行
A表更新后成
pageid pagename
1 标题一
2 标题辞1 (或标题 2 不为空就成)
3 标题3
4 标题4

就是说只要能把B表里的标题填到A表里就成，只要不是空的，怎么做？在sql里

[dawugui]

update a
set pagename = t.pagename 
from a,
(select pageid , max(pagename) pagename from b group by pageid) t
where a.pageid = t.pageid

[子陌红尘]

update A set pagename=(select top 1 pagename from B where pageid=A.pageid) from A

[dawugui]

--1
update a
set pagename = t.pagename 
from a,
(select pageid , max(pagename) pagename from b group by pageid) t
where a.pageid = t.pageid

--2
update a
set pagename = (select max(pagename) from b where pageid = a.pageid)
from a

[百年树人]

update A
set pagename=(select top 1 name from B where pageid=a.pageid order by newid())

[sdhdy]

update a set pagename=(select max(pagename) from b where a.pageid=b.pageid)  from a where isnull(a.pagename,'')=''

[dawugui]

create table A(pageid int,pagename varchar(20))
insert into a values(1 , null)     
insert into a values(2 , null)       
insert into a values(3 , null)     
insert into a values(4 , null)     
create table b(pageid int,pagename varchar(20))
insert into b values(1 ,     null)    
insert into b values(1 ,     '标题二') 
insert into b values(2 ,     '标题辞1') 
insert into b values(2 ,     '标题2') 
insert into b values(3 ,     '标题3') 
insert into b values(4 ,     '标题4') 
go

--1
update a
set pagename = t.pagename 
from a,
(select pageid , max(pagename) pagename from b group by pageid) t
where a.pageid = t.pageid


select * from a

drop table a , b

/*
pageid      pagename             
----------- -------------------- 
1           标题二
2           标题辞1
3           标题3
4           标题4

（所影响的行数为 4 行）
*/

create table A(pageid int,pagename varchar(20))
insert into a values(1 , null)     
insert into a values(2 , null)       
insert into a values(3 , null)     
insert into a values(4 , null)     
create table b(pageid int,pagename varchar(20))
insert into b values(1 ,     null)    
insert into b values(1 ,     '标题二') 
insert into b values(2 ,     '标题辞1') 
insert into b values(2 ,     '标题2') 
insert into b values(3 ,     '标题3') 
insert into b values(4 ,     '标题4') 
go

--2
update a
set pagename = (select max(pagename) from b where pageid = a.pageid)
from a

select * from a

drop table a , b

/*
pageid      pagename             
----------- -------------------- 
1           标题二
2           标题辞1
3           标题3
4           标题4

（所影响的行数为 4 行）
*/

[PB菜鸟]

study

[百年树人]

之前没有考虑B的pagename可能存在空的情况，修正一下：

---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([pageid] int,[pagename] varchar(10))
insert [A]
select 1,NULL union all
select 2,null union all
select 3,null union all
select 4,null
if object_id('[B]') is not null drop table [B]
go
create table [B]([pageid] int,[pagename] varchar(7))
insert [B]
select 1,null union all
select 1,'标题二' union all
select 2,'标题辞1' union all
select 2,'标题2' union all
select 3,'标题3' union all
select 4,'标题'
 
---更新---
update A
set pagename=(select top 1 pagename from B where pageid=a.pageid and pagename is not null order by newid())

---查询---
SELECT *FROM A

---结果---
pageid      pagename   
----------- ---------- 
1           标题二
2           标题2
3           标题3
4           标题

（所影响的行数为 4 行）

[百年树人]

再改改：

update A
set pagename=(select 
                top 1 pagename 
              from B 
              where pageid=a.pageid 
              and pagename is not null 
              and len(pagename)<>0 
              order by newid()
              )

[FNO1468]

create table a(id1 int,namea varchar(20))
create table b(id2 int,nameb varchar(20))

insert into a select 1,'' union all
         select 2,'' union all
         select 3,'' 
insert into b select 1,'' union all
         select 1,'a'union all
         select 2,'b'union all
         select 2,'bb'union all
         select 3,'cc' union all
         select 3,'c' 


update a set namea = (select top 1 nameb from b where b.id2 = a.id1 and b.nameb != '') 

select * from a

drop table a,b

id1    namea
1	a
2	b
3	cc