Delete GroupedBy 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

[Akki1]

@Ranjit Nimbalka There are 400k+ records

[Ranjit Nimbalka]

u_custom_key is this a reference field.

[Akki1]

@Ranjit Nimbalka No

[Riya Verma]

Hi @Akki1 ,

Hope you are doing great.

Step 1: Identify Duplicate Records

// Create a GlideAggregate to group records by 'u_custom_key' and count them
var duplicateQuery = new GlideAggregate('table_name');
duplicateQuery.addQuery('u_custom_key', 'ISNOTEMPTY'); // Ensure 'u_custom_key' is not empty
duplicateQuery.addAggregate('COUNT', 'u_custom_key'); // Count records in each group
duplicateQuery.groupBy('u_custom_key');
duplicateQuery.query();

Step 2: Delete Older Duplicates

while (duplicateQuery.next()) {
  var count = duplicateQuery.getAggregate('COUNT', 'u_custom_key');
  
  // Check if there are duplicates (count > 1) and proceed to delete the older one
  if (count > 1) {
    var groupKey = duplicateQuery.u_custom_key.toString();

    // Create a new GlideRecord to fetch records in the duplicate group
    var duplicateGroup = new GlideRecord('table_name');
    duplicateGroup.addQuery('u_custom_key', groupKey);
    duplicateGroup.orderByDesc('updated'); // Sort by 'updated' field in descending order
    duplicateGroup.query();

    // Delete all duplicates except the most recently updated one
    if (duplicateGroup.next()) {
      while (duplicateGroup.next()) {
        duplicateGroup.deleteRecord(); // Delete older duplicates
      }
    }
  }
}