Java method for deleting duplicate database records

There are two Repeated Records. One is a completely repeated record, that is, records with all fields repeated. The other is records with some key fields repeated, such as the Name field repeated, other fields are not necessarily repeated or can be ignored.

There are two Repeated Records. One is a completely repeated record, that is, records with all fields repeated. The other is records with some key fields repeated, such as the name field repeated, other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easy to solve.

Select distinct * from tablename

You can get the result set without repeated records.

If the table needs to delete Repeated Records (one record is retained), you can delete the record as follows:

　

Select distinct * into # tmp from tablename

Drop table tablename

Select * into tablename from # tmp

Drop table # tmp

　　

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are name and address. You must obtain the unique result set of the two fields.

　

Select identity (int, 1, 1) as autoid, * into # tmp from tablename

Select min (autoid) as autoid into # tmp2 from # tmp group by name, autoid

Select * from # tmp where autoid in (select autoid from # tmp2)

　　

The last select command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)