How to inactive the duplicate records?

Nithya Devi · 2 weeks ago

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?

Shashank_Jain · 2 weeks ago

@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!

If this works, please mark it as helpful/accepted — it keeps me motivated and helps others find solutions.
Shashank Jain – Software Engineer | Turning issues into insights

View solution in original post

AbinC · 2 weeks ago

Hi @Nithya Devi ,

To inactivate duplicate records in the risk assessment table, you can use a SQL script. Assuming you're using a database management system like MySQL or PostgreSQL, here's a sample script:

SQL Script
UPDATE risk_assessment_table
SET is_active = FALSE
WHERE (number, id) IN (
SELECT number, MAX(id)
FROM risk_assessment_table
GROUP BY number
HAVING COUNT(number) > 1
);

However, this script assumes you want to keep the record with the minimum `id` as active. If you want to keep the original record based on another criteria, you'll need to adjust the script accordingly.

Alternative Script
If you want to keep the record with the minimum `id` as active:
UPDATE risk_assessment_table ra
JOIN (
SELECT number, MIN(id) as min_id
FROM risk_assessment_table
GROUP BY number
HAVING COUNT(number) > 1
) dup ON ra.number = dup.number AND ra.id != dup.min_id
SET ra.is_active = FALSE;

Explanation
1. The subquery identifies duplicate records by grouping by the `number` field and counting the occurrences.
2. The `HAVING COUNT(number) > 1` clause filters out non-duplicate records.
3. The `UPDATE` statement sets `is_active` to `FALSE` for the duplicate records.

Precautions
1. Before running the script, make sure to back up your database.
2. Verify the script's logic and test it on a development environment to ensure it works as expected.

Additional Tips
1. Consider adding a unique constraint on the `number` field to prevent future duplicates.
2. Review your data import process to prevent accidental imports from UAT to PROD.

Please adjust the script according to your specific database schema and requirements. If you're using a different database system, the syntax might vary.

If you found my answer helpful please do mark helpful and accept the solution

Thanks,

Abin.

Nithya Devi · 2 weeks ago

Hi Abinc,

Thanks for the Valuable response. Could you please suggest java script (ServiceNow) for the duplicate records instead of SQL script.

Regards,

Nithya.

Bhuvan · 2 weeks ago

@Nithya Devi

Do you have a field that captures the data imported from UAT to Prod and differentiates from original Production records, for example created by or updated by of the records, description or created/updated of all the imported records or any other field.

If you have a filter condition, group by that field and do a clean up. Since this is Production, first validate the row count of records to be deleted and validate they are actual duplicates and not the original records. Exercise extreme caution and make sure you do not accidentally delete the original records.

As a precaution, take a XML import of all records so that in case needed it can be imported. Use below to get row count and delete multiple records from Scripts Background,

To query the records,

var gr = new GlideRecord('<table_name>');
gr.addQuery(<use your filter conditions>);
gr.query();
gs.print(gr.getRowCount());

To delete the records,

var gr = new GlideRecord('<table_name>');
gr.addQuery(<use your filter conditions>);
gr.query();
gr.deleteMultiple();

Make sure before you migrate data from UAT to Prod, do it via import set & create a transform map and always validate the coalesce before inserting records.

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

Bhuvan · 2 weeks ago

@Nithya Devi

I believe it would be better if you update the field 'active' to 'false' if the table has active field.

You can group by active is 'false' and review the duplicate records before deleting it. Use below query for update,

var gr = new GlideRecord('<table_name>');
gr.addQuery(<use your filter conditions>);
gr.query();
while (gr.next()) {
gr.active = 'false';
gr.update();
}

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan