更新问题求SQL

[mabailin]

id grp a
1 A 100
2 A 100
3 A
4 B 20
5 B
6 C 50
7 C
8 C
9 C 50
10 D
11 D 60
12 D
13 E 300
14 E 300
15 E 300


有这样一个表，id为主键grp为分组号，对于每个分组a列都应该有相同的值，但是现在有一些组中a列的值缺失了，需要参照已有的数据更新该表，填充a列的值，以下为要求的结果
id grp a
1 A 100
2 A 100
3 A 100
4 B 20
5 B 20
6 C 50
7 C 50
8 C 50
9 C 50
10 D 60
11 D 60
12 D 60
13 E 300
14 E 300
15 E 300

[chuanzhang5687]

update t1 set t1.a = t2.a
from tb t1,tb t2
where t1.grp = t2.grp
and isnull(t2.a,'')<> ''

[chuanzhang5687]

楼主结贴率老NB了

 

create table tb
(
	id	int,
	grp nvarchar(2),
	a	int
)
insert into tb values(1,'A',100)
insert into tb values(2,'A',100)
insert into tb values(3,'A',null)
insert into tb values(4,'B',20)
insert into tb values(5,'B',null)
insert into tb values(6,'C',50)
insert into tb values(7,'C',null)
insert into tb values(8,'C',null)
insert into tb values(9,'C',50)
insert into tb values(10,'D',null)
insert into tb values(11,'D',60)
insert into tb values(12,'D',null)
insert into tb values(13,'E',300)
insert into tb values(14,'E',300)
insert into tb values(15,'E',300)



update t1 set t1.a = t2.a
from tb t1,tb t2
where t1.grp = t2.grp 
and isnull(t2.a,0) <> 0

select * from tb
/*
id,grp,a
1,A,100
2,A,100
3,A,100
4,B,20
5,B,20
6,C,50
7,C,50
8,C,50
9,C,50
10,D,60
11,D,60
12,D,60
13,E,300
14,E,300
15,E,300

(15 行受影响)

*/