Database Query to Reject 2K+ Record Duplicate Suggestion : xDM

K

Katherine Isaac

started a topic 4 days ago

I need assistance with a SQL query to reject a duplicate suggestion. I have a merge ID containing over 2,000 records that I need to reject, but the UI performance becomes prohibitively slow when handling groups of this size, making it impossible to complete the action through the standard interface.

Anyone able to provide appropriate SQL syntax to reject this large duplicate suggestion directly through the database? I want to ensure the query replicates all the steps that would normally be executed if I accessed the suggested merge ID through the UI, clicked "reject," and then clicked "finish" to complete the process.

A

Alexia SIMOND

said 1 day ago

Hello Katherine,

Are you looking for something like this?

https://support.semarchy.com/support/solutions/articles/43000738257-how-to-reject-merge-suggestions-in-bulk-using-sql-in-semarchy-xdm/preview

K

Katherine Isaac

said 1 day ago

Hi Alexia, when I click that link it says the page I'm looking for doesn't exist

A

Alexia SIMOND

said 1 day ago

Hi Katherine,

Weird, let me paste its content here:

---------------------------------------------------------------------------------------------------------------------------------

How to Reject Merge Suggestions in Bulk Using SQL in Semarchy xDM

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             <##Createacontinuousloadonthemainentity##>,              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 <##Conditiononthoseyouwanttocancelsuggestion##>;

HTML

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.