update b set name = tt.name ,age = tt.age from ( select t1.id, isnull(t1.name,t2.name) as name,isnull(t1.age,t2.age) as age from a t1 inner join b t2 on t1.id = t2.id )tt where tt.id = b.id
update n set name=isnull(m.name,n.name), age =isnull(m.age ,n.age ) from a m,b n where m.id=n.id --之前将别名写错了,修正一下
先谢谢各位. 子陌同志,你上面的写法,在"如表b中有,而表a中无,则保留原有的"这一条件不满足,即是保留表b原有的数据,而不是用空覆盖掉.谢谢再请教下怎么写? ----------------------------------------------------------------------------------- --可以保留原有的呀 create table a(id int , name varchar(10), age int) insert a select 1,'abc',17 union all select 2,'def',18 union all select 3,'ghi',19 create table b(id int , name varchar(10), age int) insert b select 2,'def',8 union all select 3,'ghi',9 union all select 4,'jkl',10 goupdate n set name=isnull(m.name,n.name), age =isnull(m.age ,n.age ) from a m,b n where m.id=n.id select * from bdrop table a,b/* id name age ------------------------ 2 def 18 3 ghi 19 4 jkl 10 */
就是我用你上面的SQL执行后,当a表其中一个字段是空的时候,表b原来相应的字段是有数据的就变成空的 ----------------------------------------------------------------------------------- --SQL数据库正常的很 create table a(id int , name varchar(10), age int, home varchar(10)) insert a select 1,'abc',17,null union all select 2,'def',18,null union all select 3,'ghi',19,null create table b(id int , name varchar(10), age int, home varchar(10)) insert b select 2,'def',8,'广东' union all select 3,'ghi',9,'湖南' union all select 4,'jkl',10,'江西' goupdate n set name=isnull(m.name,n.name), age =isnull(m.age ,n.age ) from a m,b n where m.id=n.id select * from bdrop table a,b/* id name age home ----------------------------------- 2 def 18 广东 3 ghi 19 湖南 4 jkl 10 江西 */
--用CASE WHEN 就可以了 create table a(id int , name varchar(10), age int, home varchar(10)) insert a select 1,'abc',17,'' union all select 2,'def',18,'' union all select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10)) insert b select 2,'def',8,'广东' union all select 3,'ghi',9,'湖南' union all select 4,'jkl',10,'江西' goupdate n set name=case when n.name is null or n.name='' then m.name else n.name end, age =case when n.age is null or n.age='' then m.age else n.age end, home=case when n.home is null or n.home='' then m.home else n.home end from a m,b n where m.id=n.id select * from bdrop table a,b/* id name age home ----------------------------------- 2 def 18 广东 3 ghi 19 湖南 4 jkl 10 江西 */
你把表a中的name值改一个为null试试?
--更正 create table a(id int , name varchar(10), age int, home varchar(10)) insert a select 1,'abc',17,'' union all select 2,'def',18,'' union all select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10)) insert b select 2,'def',8,'广东' union all select 3,'ghi',9,'湖南' union all select 4,'jkl',10,'江西' goupdate n set name=case when m.name is not null and m.name!='' then m.name else n.name end, age =case when m.age is not null and m.age!=0 then m.age else n.age end, home=case when m.home is not null and m.home<>'' then m.home else n.home end from a m,b n where m.id=n.id select * from bdrop table a,b/* id name age home ----------------------------------- 2 def 18 广东 3 ghi 19 湖南 4 jkl 10 江西 */
对了,如果有100个字段,要set一百次了??能不能作个函数? 请再指点一下
--用动态SQL create table a(id int , name varchar(10), age int, home varchar(10)) insert a select 1,'abc',17,'' union all select 2,'def',18,'' union all select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10)) insert b select 2,'def',8,'广东' union all select 3,'ghi',9,'湖南' union all select 4,'jkl',10,'江西' godeclare @sql varchar(8000) set @sql='update n set 'select @sql=@sql+[name]+'=case when m.'+[name]+' is not null and m.'+[name]+'!='''' then m.'+[name]+' else +n.'+[name]+' end,' from syscolumns where id=object_id('b') and [name] <>'id'select @sql=left(@sql,len(@sql)-1) +' from a m,b n where m.id=n.id'exec(@sql)select * from bdrop table a,b/* id name age home ----------------------------------- 2 def 18 广东 3 ghi 19 湖南 4 jkl 10 江西 */
from
(
select t1.id, isnull(t1.name,t2.name) as name,isnull(t1.age,t2.age) as age
from a t1 inner join b t2 on t1.id = t2.id
)tt
where tt.id = b.id
set
name=isnull(m.name,n.name),
age =isnull(m.age ,n.age )
from
a m,b n
where
m.id=n.id --之前将别名写错了,修正一下
子陌同志,你上面的写法,在"如表b中有,而表a中无,则保留原有的"这一条件不满足,即是保留表b原有的数据,而不是用空覆盖掉.谢谢再请教下怎么写?
子陌同志,你上面的写法,在"如表b中有,而表a中无,则保留原有的"这一条件不满足,即是保留表b原有的数据,而不是用空覆盖掉.谢谢再请教下怎么写?
-----------------------------------------------------------------------------------
--可以保留原有的呀
create table a(id int , name varchar(10), age int)
insert a
select 1,'abc',17 union all
select 2,'def',18 union all
select 3,'ghi',19 create table b(id int , name varchar(10), age int)
insert b
select 2,'def',8 union all
select 3,'ghi',9 union all
select 4,'jkl',10
goupdate n
set
name=isnull(m.name,n.name),
age =isnull(m.age ,n.age )
from
a m,b n
where
m.id=n.id select * from bdrop table a,b/*
id name age
------------------------
2 def 18
3 ghi 19
4 jkl 10
*/
子陌同志,你上面的写法,在"如表b中有,而表a中无,则保留原有的"这一条件不满足,即是保留表b原有的数据,而不是用空覆盖掉.谢谢再请教下怎么写?
------------------------------------------------------------------------------------
楼主的意思是,a表和b表中的id并不是一一对应,而是a表中的id在b中不一定有,反之亦然?
先谢谢各位.
子陌同志,你上面的写法,在"如表b中有,而表a中无,则保留原有的"这一条件不满足,即是保留表b原有的数据,而不是用空覆盖掉.谢谢再请教下怎么写?
------------------------------------------------------------------------------------
楼主的意思是,a表和b表中的id并不是一一对应,而是a表中的id在b中不一定有,反之亦然?
------------------------------------------------------------------------------------
你的理解没错
"a表中的id在b中不一定有"这个情况下我知道怎么处理.
就是我用你上面的SQL执行后,当a表其中一个字段是空的时候,表b原来相应的字段是有数据的就变成空的了.isnull这语法我也查了一下资料,还是有点不太明白会出现这情况.
-----------------------------------------------------------------------------------
--SQL数据库正常的很
create table a(id int , name varchar(10), age int, home varchar(10))
insert a
select 1,'abc',17,null union all
select 2,'def',18,null union all
select 3,'ghi',19,null create table b(id int , name varchar(10), age int, home varchar(10))
insert b
select 2,'def',8,'广东' union all
select 3,'ghi',9,'湖南' union all
select 4,'jkl',10,'江西'
goupdate n
set
name=isnull(m.name,n.name),
age =isnull(m.age ,n.age )
from
a m,b n
where
m.id=n.id select * from bdrop table a,b/*
id name age home
-----------------------------------
2 def 18 广东
3 ghi 19 湖南
4 jkl 10 江西
*/
我这边是这样测试的,表a有个别字段是空的,表b对应的字段则有数据,执行后,表b对应字段也变成空的,这不是我想要的结果.你那边试试看
create table a(id int , name varchar(10), age int, home varchar(10))
insert a
select 1,'abc',17,'' union all
select 2,'def',18,'' union all
select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10))
insert b
select 2,'def',8,'广东' union all
select 3,'ghi',9,'湖南' union all
select 4,'jkl',10,'江西'
goupdate n
set
name=case when n.name is null or n.name='' then m.name else n.name end,
age =case when n.age is null or n.age='' then m.age else n.age end,
home=case when n.home is null or n.home='' then m.home else n.home end
from
a m,b n
where
m.id=n.id select * from bdrop table a,b/*
id name age home
-----------------------------------
2 def 18 广东
3 ghi 19 湖南
4 jkl 10 江西
*/
create table a(id int , name varchar(10), age int, home varchar(10))
insert a
select 1,'abc',17,'' union all
select 2,'def',18,'' union all
select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10))
insert b
select 2,'def',8,'广东' union all
select 3,'ghi',9,'湖南' union all
select 4,'jkl',10,'江西'
goupdate n
set
name=case when m.name is not null and m.name!='' then m.name else n.name end,
age =case when m.age is not null and m.age!=0 then m.age else n.age end,
home=case when m.home is not null and m.home<>'' then m.home else n.home end
from
a m,b n
where
m.id=n.id select * from bdrop table a,b/*
id name age home
-----------------------------------
2 def 18 广东
3 ghi 19 湖南
4 jkl 10 江西
*/
请再指点一下
create table a(id int , name varchar(10), age int, home varchar(10))
insert a
select 1,'abc',17,'' union all
select 2,'def',18,'' union all
select 3,'ghi',19,'' create table b(id int , name varchar(10), age int, home varchar(10))
insert b
select 2,'def',8,'广东' union all
select 3,'ghi',9,'湖南' union all
select 4,'jkl',10,'江西'
godeclare @sql varchar(8000)
set @sql='update n set 'select @sql=@sql+[name]+'=case when m.'+[name]+' is not null and m.'+[name]+'!='''' then m.'+[name]+' else +n.'+[name]+' end,'
from syscolumns where id=object_id('b') and [name] <>'id'select @sql=left(@sql,len(@sql)-1) +'
from
a m,b n
where
m.id=n.id'exec(@sql)select * from bdrop table a,b/*
id name age home
-----------------------------------
2 def 18 广东
3 ghi 19 湖南
4 jkl 10 江西
*/