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?

[Kiran_45]

Hello @Akki1 ,

Could you please elaborate on which table you want to write script.

Regards,

Kiran

[Samaksh Wani]

Hello @Akki1 

Plz Elaborate your Use case in detail.

[Community Alums]

Hello @Akki1 ,

As I understood you want to delete duplicate records in the table('u_dummy_table') while keeping the oldest record based on the field( 'u_name'), using the 'sys_updated_on' field to determine the oldest record.

Please refer below code to achieve your requirement:

// Replace 'your_table_name' with the actual name of your table
var tableName = 'u_dummy_table';
var fieldName = 'u_name'; // Replace 'u_name' with the field containing the duplicate value

// Create a GlideRecord object for the table
var gr = new GlideRecord(tableName);

// Query for records with duplicate 'u_name' values
gr.addEncodedQuery(fieldName + 'ISNOTEMPTY');
gr.addAggregate('COUNT', fieldName);
gr.groupBy(fieldName);
gr.addHaving('COUNT', '>', 1);
gr.query();

// Loop through the duplicate records and delete the latest ones while keeping the oldest one
while (gr.next()) {
    var duplicateName = gr.getValue(fieldName);

    // Create another GlideRecord to find the oldest record among the duplicates
    var grDuplicate = new GlideRecord(tableName);
    grDuplicate.addQuery(fieldName, duplicateName);
    grDuplicate.orderByDesc('sys_updated_on'); // Assuming you have a 'sys_created_on' field to determine the oldest record
    grDuplicate.query();

    // Skip the first (oldest) record and delete the rest
    if (grDuplicate.next()) {
        grDuplicate.next(); // Move to the second record (the latest one)
        gs.print(gr.u_number + ' : :Deleting record with ' + fieldName + ': ' + grDuplicate.getValue(fieldName));
        grDuplicate.deleteRecord();
    }
}

// Display a message indicating the script has finished
gs.print('Duplicate records cleanup completed!');

You can use the code in a ServiceNow Script Include, Business Rule, Fix script, or a Scripted REST API. Remember to test it in a non-production instance and have a data backup before running it on the production environment. Exercise caution when deleting records permanently.

Note: You can recover mistakenly deleted records from sys_rollback_context table in ServiceNow.

If this helped you in any way, please hit the like button/mark it helpful. So it will help others to get the correct solution.

regards,

Prasad

[OlaN]

Hi,

If I understood your requirement, this script should work.

Try it first, and verify, before running on PROD instances.

var incGR = new GlideRecord('incident');  // change to your tablename
incGR.orderBy('caller_id'); // change to your custom unique field 
incGR.orderByDesc('sys_updated_on');
incGR.setLimit(10);  // remove limit to run on larger datasets
incGR.query();
var unique = '';

while(incGR.next()){
  
    if (unique == incGR.getValue('caller_id')){  // replace with your custom unique field
        gs.info('delete duplicate: ' + incGR.getValue('number'));  // comment out after finished testing
        // incGR.deleteRecord();  //  remove comment after finished testing
    }
    else{
        gs.info('new unique caller: ' + incGR.getValue('number'));  // comment out after finished testing
        unique = incGR.getValue('caller_id');  // replace with your custom unique field
    }
}