如何用SQL进行指定行与指定行的更新

[redcn2004]

表名：TEST
数据：
FID FNAME BNAME
1 AAA CCC
2 AAA DDD
3 BBB 1111
4 BBB 2222
5 CCC 000
6 CCC 001

要求： 名称FNAME相同的行，更新id小的 BName为id大的BNAME值，并删除id大的行，即最终变为

FID FNAME BNAME
1 AAA DDD

3 BBB 2222

5 CCC 001

[还在加载中灬]

UPDATE TEST
SET BNAME=(SELECT TOP 1 BNAME FROM TEST T WHERE TEST.FNAME=T.FNAME ORDER BY FID DESC)

DELETE TEST
WHERE EXISTS(SELECT 1 FROM TEST AS T WHERE TEST.FNAME=T.FNAME AND TEST.FID>T.FID)

兼容SQL2000 如果是SQL2005 可以结合ROW_NUMBER

[还在加载中灬]

;WITH CTE AS(
	SELECT FID,FNAME,BNAME
		,ROW_NUMBER()OVER(PARTITION BY FNAME ORDER BY FID)RN1
		,ROW_NUMBER()OVER(PARTITION BY FNAME ORDER BY FID DESC)RN2
	FROM TEST
)
UPDATE T1
SET BNAME=T2.BNAME
FROM CTE T1
	JOIN CTE T2 ON T1.FNAME=T2.FNAME
WHERE T1.RN1=1 AND T2.RN2=1

;WITH CTE AS(
	SELECT FID,FNAME,BNAME
		,ROW_NUMBER()OVER(PARTITION BY FNAME ORDER BY FID)RN
	FROM TEST
)
DELETE CTE
WHERE RN>1

SQL 2005+