有以下数据：
srn groupcode Field30
h30511 *20111207000079* LK169849491CN
h30510 *20111207000079* NULL
u70377 *20111207000116* NULL
u70374 *20111207000116* NULL
u70375 *20111207000116* LK168318165CN
u70376 *20111207000116* NULL
把groupcode相同的对应的Field30列进行更新，要得到以下结果：
srn groupcode Field30
h30511 *20111207000079* LK169849491CN
h30510 *20111207000079* LK169849491CN
u70377 *20111207000116* LK168318165CN
u70374 *20111207000116* LK168318165CN
u70375 *20111207000116* LK168318165CN
u70376 *20111207000116* LK168318165CN
.....
.....
------解决方案--------------------
- SQL code
update aset field30=b.field30from tb a, (select groupcode,max([Field30]) from tb group by groupcode)bwhere a.groupcode=b.groupcode
------解决方案--------------------
update tb set Field30 = (select max(Field30) from tb where groupcode = t.groupcode) from tb t
------解决方案--------------------
- SQL code
create table tb( srn varchar(50), groupcode varchar(50), field30 varchar(50))goInsert into tb(srn,groupcode,field30) values('h30511','*20111207000079*','LK169849491CN')Insert into tb(srn,groupcode) values('h30510','*20111207000079*')Insert into tb(srn,groupcode) values('u70377','*20111207000116*')Insert into tb(srn,groupcode) values('u70374','*20111207000116*')Insert into tb(srn,groupcode,field30) values('u70375','*20111207000116*','LK168318165CN')Insert into tb(srn,groupcode) values('u70376','*20111207000116*')goupdate tb set field30=(select top 1 field30 from tb where groupcode=a.groupcode and field30 is not null)from tb agoselect * from tbgodrop table tb
------解决方案--------------------
错了=。=
update
a
set
field30=b.field30
from
tb a,
(select groupcode,max([Field30] Field30 ) from tb group by groupcode)b
where
a.groupcode=b.groupcode