Remove duplicate record

[VM3]

Hello Team,

I have some duplicates in Company table (core_company) [Snap attached]. I need to remove older record and keep the new one.

I tried following script but looks like I am missing something. 

Please help. 

var rec = new GlideAggregate('core_company');
rec.groupBy('name');
rec.query();
gs.info(rec.getRowCount());

var rec1;
while(rec.next()){
   rec1 = new GlideRecord('core_company');
   rec1.sortAscending('sys_created_on'); 
   rec1.addQuery('name',rec.name);
   rec1.query();
   rec1.next(); // Skip first record (Most current Record skip)
   while(rec1.next()){     
    gs.info(rec1.name+"   "+rec1.sys_created_on); //This is the duplicate record needs to be removed

   }
}

[Mohith Devatte]

Hello ,

can you try this script ?

var gr = new GlideRecord('core_company');
gr.query();
while(gr.next())
{
var cr = new GlideRecord('core_company');
cr.addQuery('sys_id','!=',gr.sys_id);
cr.addQuery('name',gr.name);
cr.query();
while(cr.next())
{
cr.deleteRecord()
}
}

PLEASE MARK MY ANSWER CORRECT IF IT HELPS YOU

[VM3]

Thanks Mohit for your quick response.

Looks like it is deleting both of the records. I want to keep one of them[Newer one and delete older one] I verified with gs.info line before deleting (Below)

var gr = new GlideRecord('core_company');
gr.query();
while(gr.next())
{
  var cr = new GlideRecord('core_company');
  cr.addQuery('sys_id','!=',gr.sys_id);
  cr.addQuery('name',gr.name);
  cr.query();
  while(cr.next()){
     gs.info(cr.name+"   "+cr.sys_created_on);
   //cr.deleteRecord();
  }
}

[0:00:02.501] Script completed in scope global: script

---

Script execution history and recovery

---

*** Script: VMware, Inc.   2022-03-11 17:15:48
*** Script: VMware   2022-05-02 15:44:21
*** Script: MongoDB   2022-03-31 21:22:21
*** Script: The pgAdmin Development Team   2022-03-31 21:20:47
*** Script: VMware   2022-03-11 17:15:48
*** Script: VMware, Inc.   2022-05-02 15:44:21
*** Script: VMware Virtual RAM   2022-05-02 15:44:21
*** Script: VMware Virtual RAM   2022-03-11 17:15:48
*** Script: The pgAdmin Development Team   2022-05-03 21:16:12
*** Script: MongoDB   2022-05-03 21:18:48

[Mohith Devatte · Accepted solution]

then can you try this i tested this and it deleted only one but yeah can you try this ?

because i am deleting in the second loop using cr.deleteRecord() which will delete second loop records and in add query sys_id != sys_id is important and above loop records should not match with below ones 

var gr = new GlideRecord('core_company');
gr.query();
while(gr.next())
{
  var cr = new GlideRecord('core_company');
cr.orderByDesc('sys_created_on');
  cr.addQuery('sys_id','!=',gr.sys_id.toString());
  cr.addQuery('name',gr.name);
  cr.query();
  while(cr.next()){
     gs.info(cr.name+"   "+cr.sys_created_on);
   //cr.deleteRecord();
  }
}

[VM3]

Hello Mohith

That looks ok now. 

Thanks,

VM