Want to keep the latest record for each user and delete the other records from the table

[SovanBanerjee]

There is one custom table where 10M+ records exist and there are multiple records for each user.

So, want to keep only the latest record for each user and delete the other records from that table.

Solution I am thinking of -

1. Need to get the sys id of latest record per user and store them into an array 

2. Then, will export the XML file from the table list with the above getting sys id's which will import after deletion

3. Then will run a scheduled job with limitation to delete the records from that table

4. After that will import the XML into the table again to only store one record per user

As there are 10M+ records exist so it is very tough to fetch the latest records per user.

Can anyone please help with the scripts or suggestions to achieve this? Any other solutions will be fine.

Thanks!

[Kieran Anson]

The exporting to xml and reimporting approach introduces risk. Ideally this would be executed as a fix script with a limit, and executed over a series of days in batches with validation post-execution. 

To confirm the requirement, your table has a reference to a user (via the sys_user table).

You only want to keep the latest record based on a date/time stamp

The other records for that user are subsequently deleted

[dhoang22]

Hi @SovanBanerjee,

We will break this down into three phases:

1. Identify and Export the target sys_ids to keep.
2. Truncate the custom table (Fastest deletion method).
3. Import the saved records back.

I will address the first phase, which is the one I think you are interested to.

Instead of exporting via a complex array of sys_ids, create a temporary True/False field on your custom table (e.g., u_keep_record).

Run this background script to mark the latest record for each user.

// Run this in Background Scripts
var ga = new GlideAggregate('u_your_custom_table'); // Replace with your table name
ga.addEncodedQuery('u_keep_record=false');
ga.addAggregate('MAX', 'sys_created_on'); // Or u_your_date_field
ga.groupBy('u_user'); // Replace with your user reference field name
ga.query();

var batchSize = 1000;
var count = 0;
var sysIdsToKeep = [];

while (ga.next()) {
    // Get the latest record for this specific user based on the MAX date
    var latestRecord = new GlideRecord('u_your_custom_table');
    latestRecord.addQuery('u_user', ga.getValue('u_user'));
    latestRecord.addQuery('sys_created_on', ga.getAggregate('MAX', 'sys_created_on'));
    latestRecord.setLimit(1);
    latestRecord.query();
    
    if (latestRecord.next()) {
        sysIdsToKeep.push(latestRecord.getUniqueValue());
    }

    // Process updates in batches to prevent memory issues
    if (sysIdsToKeep.length >= batchSize) {
        updateRecords(sysIdsToKeep);
        sysIdsToKeep = []; // Reset array
    }
}

// Update any remaining records
if (sysIdsToKeep.length > 0) {
    updateRecords(sysIdsToKeep);
}

function updateRecords(ids) {
    var grUpdate = new GlideRecord('u_your_custom_table');
    grUpdate.addQuery('sys_id', 'IN', ids.join(','));
    grUpdate.query();
    while(grUpdate.next()) {
        grUpdate.u_keep_record = true; // Flag the record to keep
        grUpdate.setWorkflow(false);   // Disable business rules/engines
        grUpdate.autoSysFields(false); // Keep original system fields
        grUpdate.updateMultiple();    
    }
}

You could modify the script above so that it runs in batches of 1,000 records and schedule it to run at regular intervals. Alternatively, you could use events and script actions to automatically trigger one batch right after another finishes.

If the provided solution is helpful, please help mark my answer as correct. Thank you.