Database Query to Reject 2K+ Record Duplicate Suggestion
K
Katherine ISAAC
started a topic
about 1 year 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.
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.
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.
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.
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.
Katherine ISAAC
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.
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:
HTMLExplanation:
WHEREclause to control which suggestions are rejected.Notes :
Tips :- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstAlexia SIMOND
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
Katherine ISAAC
Hi Alexia, when I click that link it says the page I'm looking for doesn't exist
Alexia SIMOND
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:
HTMLExplanation:
WHEREclause to control which suggestions are rejected.Notes :
Tips :-
Import Data Into Entities via Azure Data Lake
-
Recover Deleted(soft Delete) Record and Configure in Application
-
Data Quality in batch mode and real-time integration
-
Integration with analytics tools
-
Query/Load/Delete data with the REST API
-
Does the Done Tab in Inbox have a limit?
-
How Can I Trigger Enricher or Sql Procedure when deleting?
-
Matching Rules But Only The Latest Record Creates a Golden Record
-
Unstructured and Semi Structured Data in Semarchy?
-
Read CSV files from AWS S3
See all 87 topics