Repeating a field in a table in SQL duplicate record query and processing

1. Check out duplicate records

Select Duplicate Record field form data table group by repeating record field having count (repeating record field) >1

2. Duplicate records show only one record with a minimum or maximum ID value

Select id,* from data table where Houseno (select Duplicate Record field form data table group by repeating record

Field has count (repeating record field) >1)

So how do we show only one record with the smallest or largest ID for the Houseno duplicate ID value?

The key is in the WHERE clause of SQL above select Duplicate Record field form data table group by repeating record field having count (

Duplicate record field) >1
Revision changed to
Select min (id) Form data table group by repeating record field having count (repeating record field) >1

In this way, the ID minimum value is queried in the duplicate record field

So the above statement is

Select id,* from data table where Houseno (select min (id) Form data table group by repeating record field

Having count (duplicate record field) >1)

3. It's very easy to do a delete update on duplicate records.

For example, keep only one of the minimum IDs

Delete data table where ID in (the Select Max (ID) from packet Group by repeating record field has count (heavy

Complex record field) >1)


Update Operation No, it's the same.

4.group by field having count and distinct difference

DISTCT query shows that all field values are the same, one record

For example
ID Name Sex
43 111 1
44 111 1
45 111 2
46 222 2
47 222 2
48 333 1
49 333 1

SELECT DISTINCT
[Name]
, [sex]
from [Database]. [dbo]. A

To implement the above to remove the sex field to change to

SELECT DISTINCT
[Name]

from [Database]. [dbo]. A

But to get the duplicate ID minimum value is not recommended with distinct.
Summarize:

For duplicate records the key is to detect: The Group By field has the Count (field) >1
Get one of the smallest IDs (very critical): min (ID)

Repeating a field in a table in SQL duplicate record query and processing