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里
字段 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里
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 = 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
set pagename=(select top 1 name from B where pageid=a.pageid order by newid())
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 adrop 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 aselect * from adrop table a , b/*
pageid pagename
----------- --------------------
1 标题二
2 标题辞1
3 标题3
4 标题4(所影响的行数为 4 行)
*/
---测试数据---
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 行)
set pagename=(select
top 1 pagename
from B
where pageid=a.pageid
and pagename is not null
and len(pagename)<>0
order by newid()
)
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 adrop table a,bid1 namea
1 a
2 b
3 cc