Need

Sometimes users may need to reject a large number of merge suggestions quickly. Semarchy xDM is primarily designed for individual review and approval of merge suggestions through the GUI, but bulk rejection using SQL is possible, though not recommended as a standard practice 


Summarized Solution

While it's possible to reject merge suggestions in bulk using SQL, it is not the preferred method due to performance implications. The process involves directly manipulating the database via SQL queries. Users should proceed with caution and test in a non-production environment first.

For more details, please refer to Semarchy’s documentation: Managing Duplicates with SQL.


Detailed Solution

To reject merge suggestions in bulk using SQL, you can run a query similar to the one below. This example is written for an Oracle database and can be modified according to your needs.

Step-by-Step SQL Process:

INSERT INTO UM_PERSON (
            B_LOADID,
            B_SOURCEID,
            B_PUBID,
            B_CLASSNAME,
            B_CREDATE,
            B_UPDDATE,
            B_CREATOR,
            B_UPDATOR,
            ID,
            B_CONFIRMEDSDPK,
            B_XGRP,
            B_CONFIRMATIONSTATUS,
            B_ORIGINALCONFIRMATIONSTATUS
        )
        SELECT
            <##Create a continuous load on the main entity##>, 
            mdp.B_SOURCEID,
            mdp.B_PUBID,
            'Person',
            sysdate,
            sysdate,
            'SQL_REJECT_MASS',
            'SQL_REJECT_MASS',
            mdp.ID,
            mdp.ID,
            (SELECT SYS_GUID() FROM dual),
            'CONFIRMED',
            'NOT_CONFIRMED'
        FROM md_person mdp
        WHERE <##Condition on those you want to cancel suggestion##>;

Explanation:

  • UM_PERSON: This table is where the rejection data will be inserted.
  • SYS_GUID(): Generates a unique identifier for tracking the operation.
  • Conditions: Customize the conditions in the WHERE clause to control which suggestions are rejected.


Notes:

  • Performance Impact: This query can be resource-intensive and should be tested in a non-production environment first. Running it on a large dataset can cause performance issues.
  • Non-Standard Procedure: Bulk rejection via SQL is not the standard procedure. The typical approach in Semarchy is to review and reject each merge suggestion manually through the UI.


Tips:

  • Matching Rules: If you are frequently rejecting certain suggestions, consider adjusting your matching rules to reduce the number of invalid merge suggestions.
  • SQL Optimization: Narrow down the conditions in your SQL query to affect only the necessary records. This helps minimize performance degradation.