How to delete duplicates based on 2 fields

Dead Blade · ‎02-24-2019

Hi,

So I've scrubbed through the forums and found many scripts to delete duplicate records, but they are all based on 1 field. Some of which are using GlideAggregate. So I need a script to delete records based on several field criteria. Example

Field 1 Field 2

John Smith

John Doe

John Smith

John Johnson

Delete the Duplicate John Smiths.

I will be using this script as a fix script running on a schedule as maintenance for an m2ms table. The fields are reference fields if that matters.

aashishdasari · ‎02-25-2019

Blade,

Try using the below code:

var optionsObj=JSON.parse(FindDuplicateRecords());

   for (var i = 0; i < optionsObj.length; i++) {

       var gr=new GlideRecord('u_m2m_authorize_servers');
       gr.addQuery('u_group',optionsObj[i].group);
       gr.addQuery('u_server',optionsObj[i].server);
       gr.query();
       var deleteCount = gr.getRowCount();
       while(gr.next()) {

if(deleteCount!=1)

{
           gr.deleteRecord();
           deleteCount--;
           gs.info('Record: ' + gr.optionsObj[i].group + gr.optionsObj[i].server + ' Deleted');
           gs.info('Records Deleted = ' + deleteCount);

}
}
}

If the solution is helpful please mark it as helpful

View solution in original post

asifnoor · ‎02-24-2019

Hi,

If these fields are static like john smiths, then I suggest you to get the sys_ids of those 2 fields from the respective reference tables. Considering the reference field, the sys_id will be same for all the records.

Then execute this script like below

var gr=new GlideRecord("your_table");
gr.addQuery("field1","field1_sys_id");
gr.addQuery("field2","field2_sys_id");
gr.query();
while(gr.next()) {
gr.deleteRecord();
}

Mark the comment as correct answer if this helps.

Dead Blade · ‎02-24-2019

I thought it would be that easy as well, but...

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group','cf34f5cbdb50ef00b6e69b3c8a961966');
gr.addQuery('u_server','8334f5cbdb50ef00b6e69b3c8a96197c');
gr.query();
var deleteCount = 0;
while(gr.next()) {
gr.deleteRecord();
deleteCount++;
gs.print('Record: ' + gr.u_group + gr.u_server + ' Deleted');
}
gs.print('Records Deleted = ' + deleteCount);

When I run this, I get 0 records deleted. And I checked the table and the duplicates are still there as well.

Community Alums · ‎02-24-2019

Hi,

You can use either of below codes.

1) Referring the sys id.

gr.addQuery('u_group.sys_id','cf34f5cbdb50ef00b6e69b3c8a961966');
gr.addQuery('u_server.sys_id','8334f5cbdb50ef00b6e69b3c8a96197c');

2) Use the names of group and servers.

gr.addQuery('u_group','MyGroupName');
gr.addQuery('u_server','MyServerName');

Let me know if you still face issues.

Cheers,

Hardit Singh

Dead Blade · ‎02-25-2019

Still not working.