Thread Details
  • State Not Answered
  • Replies 4 replies
  • Subscribers 372 subscribers
  • Views 384 views
  • Users 0 members are here
Forum Actions
Related

Duplicate records in “CX_List_CP”

Informática Cimertex

We are having problems with duplicate data in the customized Postal Codes module “CX_List_CP”.

This module is only associated with the accounts module.

We need to delete the duplicate records, but we have situations where both have associated accounts.

How can we delete the duplicate records without losing the link to the accounts?

In the case of a duplicate with no associated account, one of the records can be deleted.

  • 0 Sugar Employee Badge

    Hello  , 

    Thanks for you question.
    Can you clarify what kind of relationship you have between CX_List_CP module and accounts and the name of that relationship ?

    Cheers, 

    André 

  • 0 in reply to Andre Serrazina

    Hi André Serrazina,

    Thanks for your answer.

     

    The relationship between CX_List_CP module and accounts is one too many and his name is cx_lista_cp_accounts.

     

  • 0 Sugar Employee Badge in reply to Informática Cimertex

    Hello  , 

    Thanks for waiting for my update.

    I’ve been testing this locally and if I understand your requirement correctly, you should be able to resolve it using SQL in two steps.

    Step 1: Update the relationship table to keep unique cx_lista_cp entries where name is duplicated.

    I used the standard naming convention for the relationship table, so you might need to adapt it for your instance.


    UPDATE cx_lista_cp_accounts_c link
JOIN (
    SELECT clc.id AS old_id,
           keeper.id AS new_id
    FROM cx_lista_cp clc
    JOIN (
        SELECT name, MIN(id) AS id
        FROM cx_lista_cp
        WHERE deleted = 0
        GROUP BY name
        HAVING COUNT(*) > 1
    ) keeper ON clc.name = keeper.name
    WHERE clc.deleted = 0
      AND clc.id != keeper.id
) map ON link.cx_lista_cp_accountscx_lista_cp_ida = map.old_id
SET link.cx_lista_cp_accountscx_lista_cp_ida = map.new_id
WHERE link.deleted = 0

    Step 2: Soft-delete all duplicate cx_lista_cp records that are not present in the relationship table. 

    UPDATE cx_lista_cp
SET deleted = 1,
    date_modified = NOW()
WHERE id IN (
    SELECT id FROM (
        SELECT clc.id
        FROM cx_lista_cp clc
        LEFT JOIN cx_lista_cp_accounts_c link
            ON clc.id = link.cx_lista_cp_accountscx_lista_cp_ida
            AND link.deleted = 0
        JOIN (
            SELECT name, MIN(id) AS id
            FROM cx_lista_cp
            WHERE deleted = 0
            GROUP BY name
            HAVING COUNT(*) > 1
        ) keeper ON clc.name = keeper.name
        WHERE clc.deleted = 0
          AND clc.id != keeper.id
          AND link.cx_lista_cp_accountscx_lista_cp_ida IS NULL
    ) deletable
)

    Step 3: Check if there are still duplicates present.

    You can do this using the following query:


    List Duplicates:

SELECT name, COUNT(*) AS duplicate_count
FROM cx_lista_cp
WHERE deleted = 0
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC

    As you’re on SugarCloud, you’ll need to create a support case to run these queries. I recommend refreshing a clone from production and testing the queries there first to ensure they produce the expected results.

    Once confirmed, you can request support to execute them in production.

    I hope this helps.

    Cheers,

    André

  • 0 in reply to Andre Serrazina

    Thanks André Serrazina,

    This procedure works fine for us.