SQL deletes duplicate and SQL duplicate records in a table.

SQL deletes duplicate and SQL duplicate records in a table.

I have encountered the problem of Repeated Records in the table. The fastest way to directly write SQL statements to delete the records is not to copy them to the excel table slowly for manual searching.

The following SQL statement finds duplicate records and records with the smallest ID value in Repeated Records (IDs in the table are self-increasing)

Select MIN (ID) as id, StructSN, Date, UserID, StarCount, COUNT (StructSN) as cfrom T_Dor_StructStar where Date> = '2013' group by StructSN, Date, UserID, starCounthaving COUNT (StructSN)> 1

Then you can directly Delete the record. The basic principle is to find each record of the record with the minimum duplicate id, and delete the remaining record.

Delete from T_Dor_StructStarwhere ID in (select s. ID from T_Dor_StructStar s, (select MIN (ID) as id, StructSN, Date, UserID, StarCount, COUNT (StructSN) as cfrom T_Dor_StructStar where Date> = '20140901' group by StructSN, date, UserID, StarCounthaving COUNT (StructSN)> 1) awherea. date = s. dateand. structSN = s. structSNand. userID = s. userIDand. starCount = s. starCountand. id! = S. ID)

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!