How to inactive the duplicate records?

[Nithya Devi]

How to write the script to inactive the duplicate records on risk assessment table? Due to the accidental import of risk assessment data from UAT to PROD the duplicates found on production env. where number is the unique field on the risk assessment table. I want the make the original one as true and duplicate to be false. Could anyone suggest script to fix the duplicates without impacting other original records?

[Bhuvan]

@Nithya Devi 

Did you get a chance to review this ?

As per community guidelines, you can accept more than one answer as accepted solution. If my response helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Shashank_Jain · Accepted solution]

@Nithya Devi , Try this script and tell me if this works.

var ga = new GlideAggregate('risk_assessment');
ga.addAggregate('COUNT', 'number');
ga.groupBy('number');
ga.addHaving('COUNT', '>', 1);  // only get duplicates
ga.query();
while (ga.next()) {
    var dupNumber = ga.getValue('number');

    var gr = new GlideRecord('risk_assessment');
    gr.addQuery('number', dupNumber);
    gr.orderBy('sys_created_on'); // oldest (original) will come first
    gr.query();

    var isFirst = true;
    while (gr.next()) {
        if (isFirst) {
            // Keep the first/original active
            gr.active = true;
            gr.update();
            isFirst = false;
        } else {
            // Mark duplicates inactive
            gr.active = false;
            gr.update();
        }
    }
}
gs.print("Duplicate cleanup complete");

Hope it helps!

[Nithya Devi]

HI Shashank,

Thanks for your response.Let me try this on my pdi and let you know it works.

Regards,

Nithya.

[Shashank_Jain]

@Nithya Devi  , Sure if it works, please mark it as accepted.