求同一个表的UPDATE语句..

[xwqjamky]

id ksbm ysbm lb yzlb fy
18367 0305 0203 6 1 2349.0000
18367 0305 0811 6 7 2349.0000
18367 0302 0811 6 9 349.0000
18367 6 16 2000.0000
18367 6 17 2000.0000
18369 0511 1650 6 1 63.0000
18369 6 7 63.0000
18369 6 9 63.0000
18370 1511 2400 6 1 2842.0000

求update 语句 同ID的并且KSBM AND YSBM不为空的第一条数据的KSBM ,YSBM 数据 更新到同ID而KSBM AND YSBM为空的数据.update 后结果，不知道明不明白．再解释．谢谢


id ksbm ysbm lb yzlb fy
18367 0305 0203 6 1 2349.0000
18367 0305 0811 6 7 2349.0000
18367 0302 0811 6 9 349.0000
18367 0305 0203 6 16 2000.0000
18367 0305 0203 6 17 2000.0000
18369 0511 1650 6 1 63.0000
18369 0511 1650 6 7 63.0000
18369 0511 1650 6 9 63.0000
18370 1511 2400 6 1 2842.0000

[leo_lesley]

update 表
set ksbm=(select top 1 ksbm from 表 where a.id=id),ysbm=(select top 1 ysbm from 表 where a.id=id)
from 表 a
where kdbm is null and ysbm is null 

[ping3000]

create table test(id int, ksbm nvarchar(10),ysbm nvarchar(10),lb int,yzlb int,fy float)

insert into test(id,ksbm,ysbm,lb,yzlb,fy)
select 18367,'0305','0203',6,1,2349.0000 union all
select 18367,'0305','0811',6,7,2349.0000 union all
select 18367,'0302','0811',6,9,349.0000 union all
select 18367,null,null,6,16,2000.0000 union all
select 18367,null,null,6,17,2000.0000 union all
select 18369,'0511','1650',6,1,63.0000 union all
select 18369,null,null,6,7,63.0000 union all
select 18369,null,null,6,9,63.0000 union all
select 18370,'1511','2400',6,1,2842.0000

update test
set ksbm = (select top 1 ksbm from test where id = t.id and ksbm is not null and ysbm is not null),
ysbm = (select top 1 ysbm from test where id = t.id and ksbm is not null and ysbm is not null)
from test t
where t.ksbm is null and t.ysbm is null

drop table test

[xwqjamky]

试过不行喔

[一者仁心]

update tab2 set
ksbm=(select top 1 ksbm from tb1 where tb1.id=tb2.id and tb1.ksbm<>null and tb1.ysbm<>null),
ysbm=(select top 1 ysbm from tb1 where tb1.id=tb2.id and tb1.ksbm<>null and tb1.ysbm<>null)
where tab2.ksbm=null and tab2.ysbm=null

[xwqjamky]

更新记录为0条

[一者仁心]

update tab2 set 
ksbm=(select top 1 ksbm from tb1 where tb1.id=tb2.id and tb1.ksbm<>null and tb1.ysbm<>null),
ysbm=(select top 1 ysbm from tb1 where tb1.id=tb2.id and tb1.ksbm<>null and tb1.ysbm<>null)
where tab2.ksbm=null and tab2.ysbm=null

[xwqjamky]

36万条记录运行很久啊

[xwqjamky]

完成．．以上都正确．．结贴