Delete duplicate records

[Akki1]

Hi,

I have a field 'u_custom_key' and when I groupby my records with this field there are records which are having count 2 so I want to delete one of them on the basis of updated field, I want to delete the old one and keep the new one.There are approx 400k records.

If anyone can help with the working script?

[Riya Verma]

Hi @Akki1 ,

Hope you are doing great.

! To achieve the task of removing duplicates based on the 'u_custom_key' field and retaining the most recently updated record, we can follow these steps:

1. Identify the duplicate records and their counts using the 'u_custom_key' field.
2. For each duplicate group, sort the records based on the 'updated' field in descending order (newest first).
3. Keep the first record (newest) and delete the rest within each duplicate group.

// Step 1: Query to identify duplicate records and their counts
var grDupRecords = new GlideAggregate('your_table_name'); // Replace 'your_table_name' with the actual name of your table.
grDupRecords.addAggregate('COUNT', 'u_custom_key');
grDupRecords.groupBy('u_custom_key');
grDupRecords.addHaving('COUNT', '>', 1);
grDupRecords.query();

// Step 2: Process each duplicate group and delete old records
while (grDupRecords.next()) {
    var dupKey = grDupRecords.u_custom_key.getDisplayValue();
    var grDupGroup = new GlideRecord('your_table_name');
    grDupGroup.addQuery('u_custom_key', dupKey);
    grDupGroup.orderByDesc('updated'); // Sorting by 'updated' field in descending order (newest first)
    grDupGroup.query();

    var deleteCounter = 0;
    while (grDupGroup.next()) {
        if (deleteCounter > 0) {
            grDupGroup.deleteRecord(); // Step 3: Deleting old records (all except the first one)
        }
        deleteCounter++;
    }
}

[Community Alums]

Hi @Akki1 ,

Did you get a chance to review my response?

Was my answer correct? Did you implement it?

So as to help others in the community, please mark my answer as correct and/or helpful.

Thanks,

Prasad