query to update redundent records leaving one

[fikir]

Hello,

I have a table which has redundent records, I want to update those records to null but leaving one

tbl

id name
1 aa
1 aa
1 aa
2 bb
2 bb
3 cc

I wrote this query to do the task but my query statts updating from the first one and leaving the last record but I want it to be the other way, leaving the first one and updating the rest

SET ROWCOUNT 1
SELECT 1
WHILE (@@ROWCOUNT > 0)
BEGIN 
 UPDATE TBL 
 SET NAME = NULL
 FROM TBL T
 WHERE 1 < (SELECT COUNT(*) FROM TBL T2
            WHERE T.ID = T2.ID 
	    AND T.NAME = T2.NAME 			 	)
 END
SET ROWCOUNT 0

how can I modify this to solve my problem

thanks

 

[fikir]

forgot to write the output

the output should look like

id name
1 aa
1 null
1 null
2 bb
2 null
3 cc

not
id name
1 null
1 null
1 aa
2 null
2 bb
3 cc

thanks,

 

[monksnake]

id name
1 aa
1 null
1 null
2 bb
2 null
3 cc

not
id name
1 null
1 null
1 aa
2 null
2 bb
3 cc

This is the same data, just ordered differently.

<.

 

[fikir]

It should be displayed in the first one, not the second

thanks

 

[tran008]

fikir,

Like monksnake stated, the data is the same.
if you want to result in QA you need to add the
order by id, name desc:

select * from table
order by id, name desc

 

[ESquared]

Rows in a database are by definition unordered, unless there is a clustered index.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]