bulk delete

I have data in the User table with these rows UserID(PK),Usernumber,Username where userid is the primarykey

1 101 Tom

2 101 Tom

3 102 Dick

4 102 Dick

5 103 Harry

6 103 Harry

Now I want to get out put with UerId(PK),UserNumber and UserName, deleting the repeated(second occurance) row, like this...

1 101 Tom

3 102 Dick

5 103 Harry

Can any one let me know.

Thanks in adv

Hello,

Try this to get the output you request:

select min(UserID) as UserID, Usernumber, Username

from TableName

group by Usernumber, Username

order by UserID

Then, to delete the other occurances, do this:

delete from TableName

where UserID not in (select min(UserID) from TableName group by Usernumber, Username)

Hope this helps.

Jarret

|||

Thanks jarret,

It worked.