Jump to content

Delete all but one duplicate record?


This topic is 7261 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi, just wondering if it's possible to write a script to find multiple duplicate records (ie. everyone with the surname 'adam' and 'bert' and 'cat' etc.), then delete all but one of each duplicate record (ie. leave one adam, one bert, one cat ...?

Of a database of 3000 records, 1500 are duplicates (argh).

Any help gratefully appreciated. In the meantime, it's back to the manual ... *sigh*

Cheers,

Link to comment
Share on other sites

Assuming

1- all other data fields for the duplicate records are the same (otherwise it will be more difficult to script which records may (not) be deleted)

2- a UniqueRecID number field exists with a unique number for each record e.g. a serial number field, if not existing you could create this field first and fill it with a serial number using the Replace command

The following script would mark all but the firs records with a duplicate surname. You could make the script directly delete the duplicates but this way you can check visually first before manually deleting the duplicates. (2nd you avoid issues with a looping script that either omits or deletes records, there have been previous posts explaining the propper way to do that it you want to find out.)

- Create a selfjoin-relationship on the field surname (if that is the field that should be made unique)

(probably best to set it to "unique values only" in define fields so no new duplicates can be entered)

- Create a field "DuplicateCheck"

- Create a script with the following steps

- find all

- go first record

- loop

- if (count(Selfjoin::Surname) > 1) and (UniqueRecID > min(Selfjoin::UniqueRecID))

- set field DuplicateCheck = "Duplicate"

- else

- set field DuplicateCheck = "Unique"

- end if

- go next record (exit after last)

Link to comment
Share on other sites

This topic is 7261 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.