Self Join

[shultz]

Can anybody tell me how to delete duplicate records from a table.

I have the following fields in "tblAction" table.

ID - Auto Number
CSRSR - varchar(20)
Action - varchar(2000)

And here, I want to delte those records where "Action" has been repeated or duplicated.

thanks a lot.

 

[balves]

If you want to keep the one with the highest Id:

Delete tblAction
From tblAction A Inner Join (Select Action, Max(Id) as Max_Id From tblAction Group By Action) B
On A.Action = B.Action
Where A.Id <> B.Max_Id

Or change it to Min() if you want the lowest Id.