«Cannot share customer accounts globally because some customer accounts with the same emails exist on multiple websites and cannot be merged»

[dmitrii-fediuk]

Related to https://github.com/thehcginstitute-com/m1/issues/594#issuecomment-2106188190

[dmitrii-fediuk]

Step 1

DELETE FROM customer_entity WHERE entity_id IN (
    SELECT id1 FROM (
        SELECT
            c1.entity_id as 'id1'
            ,COUNT(a1.entity_id) as 'a1'
            ,COUNT(o1.entity_id) as 'o1'
            ,COUNT(a2.entity_id) as 'a2'
            ,COUNT(o2.entity_id) as 'o2'
        FROM customer_entity c1
        JOIN customer_entity c2
            ON c1.email = c2.email AND c1.entity_id <> c2.entity_id AND c1.website_id < c2.website_id
        LEFT JOIN customer_address_entity a1
            ON c1.entity_id = a1.parent_id
        LEFT JOIN customer_address_entity a2
            ON c2.entity_id = a2.parent_id
        LEFT JOIN sales_flat_order o1
            ON o1.customer_id = c1.entity_id
        LEFT JOIN sales_flat_order o2
            ON o2.customer_id = c2.entity_id
        GROUP BY c1.entity_id, c2.entity_id
        HAVING 0 = a1 AND 0 = o1 AND 0 <> a2 AND 0 <> o2
        ORDER BY c2.created_at DESC
    ) as t
);

[dmitrii-fediuk]

Step 2

DELETE FROM customer_entity WHERE entity_id IN (
    SELECT id1 FROM (
        SELECT
            c1.entity_id as 'id1'
            ,COUNT(a1.entity_id) as 'a1'
            ,COUNT(o1.entity_id) as 'o1'
            ,COUNT(a2.entity_id) as 'a2'
            ,COUNT(o2.entity_id) as 'o2'
        FROM customer_entity c1
        JOIN customer_entity c2
            ON c1.email = c2.email AND c1.entity_id <> c2.entity_id AND c1.website_id < c2.website_id
        LEFT JOIN customer_address_entity a1
            ON c1.entity_id = a1.parent_id
        LEFT JOIN customer_address_entity a2
            ON c2.entity_id = a2.parent_id
        LEFT JOIN sales_flat_order o1
            ON o1.customer_id = c1.entity_id
        LEFT JOIN sales_flat_order o2
            ON o2.customer_id = c2.entity_id
        GROUP BY c1.entity_id, c2.entity_id
        HAVING 0 = o1 AND 0 <> a2 AND 0 <> o2
        ORDER BY c2.created_at DESC
    ) as t
);

[dmitrii-fediuk]

Step 3

DELETE FROM customer_entity WHERE entity_id IN (
    SELECT id1 FROM (
        SELECT
            c1.entity_id as 'id1'
            ,COUNT(a1.entity_id) as 'a1'
            ,COUNT(o1.entity_id) as 'o1'
            ,COUNT(a2.entity_id) as 'a2'
            ,COUNT(o2.entity_id) as 'o2'
        FROM customer_entity c1
        JOIN customer_entity c2
            ON c1.email = c2.email AND c1.entity_id <> c2.entity_id AND c1.website_id < c2.website_id
        LEFT JOIN customer_address_entity a1
            ON c1.entity_id = a1.parent_id
        LEFT JOIN customer_address_entity a2
            ON c2.entity_id = a2.parent_id
        LEFT JOIN sales_flat_order o1
            ON o1.customer_id = c1.entity_id
        LEFT JOIN sales_flat_order o2
            ON o2.customer_id = c2.entity_id
        GROUP BY c1.entity_id, c2.entity_id
        HAVING 0 = o1 AND 0 <> o2
        ORDER BY c2.created_at DESC
    ) as t
);

[dmitrii-fediuk]

Step 4

DELETE FROM customer_entity WHERE entity_id IN (
    SELECT id1 FROM (
        SELECT
            c1.entity_id as 'id1'
            ,COUNT(a1.entity_id) as 'a1'
            ,COUNT(o1.entity_id) as 'o1'
            ,COUNT(a2.entity_id) as 'a2'
            ,COUNT(o2.entity_id) as 'o2'
        FROM customer_entity c1
        JOIN customer_entity c2
            ON c1.email = c2.email AND c1.entity_id <> c2.entity_id AND c1.website_id < c2.website_id
        LEFT JOIN customer_address_entity a1
            ON c1.entity_id = a1.parent_id
        LEFT JOIN customer_address_entity a2
            ON c2.entity_id = a2.parent_id
        LEFT JOIN sales_flat_order o1
            ON o1.customer_id = c1.entity_id
        LEFT JOIN sales_flat_order o2
            ON o2.customer_id = c2.entity_id
        GROUP BY c1.entity_id, c2.entity_id
        HAVING 0 = a1 AND 0 = o1 AND 0 <> a2
        ORDER BY c2.created_at DESC
    ) as t
);

[dmitrii-fediuk]

Result