How to delete repeated fields in a table

How to delete duplicate records in a table and retain only one of them [posting 12:02:05] font size: large, medium, and small

There are several identical records in thousands of records. How can I use SQL statements to delete duplicates? Thank you!
1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).
Select * from people
Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)

2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
Delete from people
Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)
And rowid not in (select Min (rowid) from people group by peopleid having count (peopleid)> 1)

3. Search for redundant duplicate records in the table (multiple fields)
Select * From vitae
Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from vitae
Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)
And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * From vitae
Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)
And rowid not in (select Min (rowid) from vitae group by peopleid, seq having count (*)> 1)

For example, there is a field "name" in Table A, and the "name" values may be the same between different records,
Now, you need to query items with duplicate "name" values between records in the table;
Select name, count (*) from a group by name having count (*)> 1

If the gender is also the same, the statement is as follows:
Select name, sex, count (*) from a group by name, sex having count (*)> 1

2. Group by Method

Query data:
Select count (Num), max (name) from student -- lists the number of repeated records and its name attribute
Group by num
Having count (Num)> 1 -- group by num to find the num column in the table, that is, more than once
Delete data:
Delete from student
Group by num
Having count (Num)> 1
In this way, all duplicates are deleted.

3. Use the distinct method-useful for small tables

Create Table table_new as select distinct * From Table1 minux
Truncate table Table1;
Insert into Table1 select * From table_new;


Delete select a. * From flrk1 a inner join flrk1 B on A. Record Number = B. Record Number and
(A. [ID] = B. [ID] and A. warehouse receiving date = B. warehouse receiving date and A. operation time = B. Operation Time)

Delete from flrk1 where record number in
(Select Min (Record Number) from flrk1 group by record number having count (Record Number)> 1)


Table A structure:

Id RQ SJ C
--------------------------------------------
1 2005-07-14 14:20:50 A1
2 2005-02-15 05:12:23 A1
3 2005-07-14 14:20:50 A1
4 2005-06-16 16:16:16 A2
5 16:16:16 A2
6 05:10:35 A3
7 05:12:23 A1

--------------------------------------------

Find one SQL statement and delete the same record for the RQ, SJ, and C fields in table.

Result:

Id RQ SJ C
--------------------------------------------
1 2005-07-14 14:20:50 A1
2 2005-02-15 05:12:23 A1
4 2005-06-16 16:16:16 A2
6 05:10:35 A3
--------------------------------------------

Delete from a where id not in (select Min (ID) from a group by RQ, SJ, c)

Delete A from TB a inner join TB as B on. FID <B. FID and. C = B. C and. RQ = B. RQ and. SJ = B. SJ

Delete from a t
Where exists (select 1 from a where ID <A. ID and SJ = T. SJ and RQ = T. RQ and c = T. C)


Method 1

Declare @ Max integer, @ ID integer
Declare cur_rows cursor local for Select Main field, count (*) from table name group by main field having count (*)> 1
Open cur_rows
Fetch cur_rows into @ ID, @ Max
While fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ Max
Delete from table name where primary field = @ ID
Fetch cur_rows into @ ID, @ Max
End
Close cur_rows
Set rowcount 0

Method 2

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

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

Select distinct * From tablename


You can get the result set without repeated records.
If the table needs to delete duplicate 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)