Check the duplicate records

[Dimitris]

Hello. There are a lot of duplicate records in a table. I used the duplicate
wizard to find them. I also have a checkbox named "dupli". What I need is to
have the duplicate records checked. All of them but one. So for example if
"John Smith" is entered 3 times, I want two of the "John Smith" records
checked and one unchecked.

Can someone help me? Have in mind that I used "Firstname" "Lastname" and
"Address" to find the duplicate records and I used the duplicate wizard since
I don't know any other way.

Thank you
Dimitris

 

[louisjohnphillips]

Hello. There are a lot of duplicate records in a table. I used the duplicate
wizard to find them. I also have a checkbox named "dupli". What I need isto
have the duplicate records checked. All of them but one. So for example if
"John Smith" is entered 3 times, I want two of the "John Smith" records
checked and one unchecked.

Can someone help me? Have in mind that I used "Firstname" "Lastname" and
"Address" to find the duplicate records and I used the duplicate wizard since
I don't know any other way.

Thank you
Dimitris

Let's hope there is a unique column in this table. If so, try:

Update MyTable
set dupli = -1
where exists
( select 'true'
from MyTable as A
where A.LastName = MyTable.LastName
and A.FirstName = MyTable.FirstName
and A.Address = MyTable.Address
and A.UniqueColumn > MyTable.UniqueColumn )

The -1 value will appear as a check in the dupli column.