I Want to Delete Duplicate Records from Table

[Nitesh A]

Using load data, i had loaded 800 records in my sys_choice table.

Again i had loaded the file having 100 records.

But that 100 records already inserted during import of 800 records.

so know i have total 900 records in which 100 are duplicate records.

and i want to delete those 100 duplicate records.

please anyone having solution for this.

[peterh_smith]

In the future, you can avoid the duplicates one of two ways:

1. If you are importing using a transform map, make sure table, element, language, value, and label are all marked to coalesce.
2. If you are using the Import mechanism, first ask to update, and export the template.   Make modifications in that template.   Aftwarwards, split the workbook into records that have sys_id set, and those that don't.   Import/update the sheet with sys_ids, and Import/insert the sheet without.

I sure wish there was an Import/be-smart-about-it feature that would insert blank sys_ids and update supplied sys_ids.

[Ankur Bawiskar]

Hi Nitesh,

Any update on this?

Can you mark answer as correct, helpful and hit like if you were able to achieve the requirement. This helps in removing this question from unanswered list and helps users to learn from your thread. Thanks in advance.

Regards

Ankur

[Imran1]

Hi Ankur,

I tried the below script in likes of yours. It didn't worked for me. I am trying to delete a duplicate record upon insertion via a business rule

Thanks for your input.

var delGr = new GlideAggregate(current.getTableName());
delGr.groupBy('u_action_type'); // This is a choice field in the table
delGr.addQuery('u_record_id',current.u_record_id);
delGr.addQuery('sys_created_on', current.sys_created_on);
delGr.addQuery('u_user', current.u_user);
delGr.query();
var dGr;
while (delGr.next()) {
dGr = new GlideRecord(current.getTableName());
dGr.addQuery('u_record_id',delGr.u_record_id);
dGr.addQuery('sys_created_on', delGr.sys_created_on);
dGr.addQuery('u_user', delGr.u_user);
dGr.query();
dGr.next();
while (dGr.next()) {
dGr.deleteRecord();
}
}

Regards,
Imran

[Imran1]

Hi Ankur,

The below script has worked for me.

var delGr = new GlideRecord(current.getTableName());
delGr.addQuery('u_record_id',current.u_record_id);
delGr.addQuery('sys_created_on', current.sys_created_on);
delGr.addQuery('u_user', current.u_user);
delGr.query();
var dGr;
var count=0;
while (delGr.next()) {
count = delGr.getRowCount();
}
if(count=='2'){
dGr = new GlideRecord(current.getTableName());
dGr.addQuery('u_record_id',delGr.u_record_id);
dGr.addQuery('sys_created_on', delGr.sys_created_on);
dGr.addQuery('u_user', delGr.u_user);
dGr.query();
dGr.next();
while (dGr.next()) {
dGr.deleteRecord();
}
}

Thanks for reading through

Regards,
Imran

[Koushik Ningara]

Hi Nitesh,

I understand the concern , same thing happened to me also,  here's the code launch yourself up;)..works just like the filters

var userRoleGA = new GlideAggregate('sys_user_has_role');
userRoleGA.addQuery('user','sys_id');  //for particular user
userRoleGA.groupBy('role');
userRoleGA.query();
var userRoleGR;
while (userRoleGA.next()) {
userRoleGR = new GlideRecord('sys_user_has_role'); //associated multiple roles..
userRoleGR.addQuery('role', userRoleGA.role);

userRoleGR.addNullQuery('userRoleGA.granted_by');  //Not granted by usually problem caused by fix script 
userRoleGR.query();
userRoleGR.next();
while (userRoleGR.next() && ) {
userRoleGR.deleteRecord();
}
}//loop end