Magento 2 - Remove duplicate store view-specific product and category data

1_product_queries.sql

-- catalog_product_entity_datetime
SELECT a.*
FROM (SELECT * FROM catalog_product_entity_datetime) AS a
         -- This inner join finds all store view-specific rows that exactly match the global scope value. The b.store_id = 0 conditional is key as it targets the global row
         INNER JOIN (SELECT * FROM catalog_product_entity_datetime) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_product_entity_decimal
SELECT a.*
FROM (SELECT * FROM catalog_product_entity_decimal) AS a
         INNER JOIN (SELECT * FROM catalog_product_entity_decimal) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_product_entity_int
SELECT a.*
FROM (SELECT * FROM catalog_product_entity_int) AS a
         INNER JOIN (SELECT * FROM catalog_product_entity_int) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_product_entity_text
SELECT a.*
FROM (SELECT * FROM catalog_product_entity_text) AS a
         INNER JOIN (SELECT * FROM catalog_product_entity_text) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_product_entity_varchar
SELECT a.*
FROM (SELECT * FROM catalog_product_entity_varchar) AS a
         INNER JOIN (SELECT * FROM catalog_product_entity_varchar) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;


#
# These queries handle deleting the values duplicated at the specific store view scopes
#
DELETE FROM `catalog_product_entity_datetime`
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids
    FROM (SELECT * FROM catalog_product_entity_datetime) AS a
             INNER JOIN (SELECT * FROM catalog_product_entity_datetime) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_product_entity_decimal`
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids
    FROM (SELECT * FROM catalog_product_entity_decimal) AS a
             INNER JOIN (SELECT * FROM catalog_product_entity_decimal) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_product_entity_int`
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids
    FROM (SELECT * FROM catalog_product_entity_int) AS a
             INNER JOIN (SELECT * FROM catalog_product_entity_int) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_product_entity_text`
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids
    FROM (SELECT * FROM catalog_product_entity_text) AS a
             INNER JOIN (SELECT * FROM catalog_product_entity_text) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_product_entity_varchar`
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids
    FROM (SELECT * FROM catalog_product_entity_varchar) AS a
             INNER JOIN (SELECT * FROM catalog_product_entity_varchar) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);


#
# delete all null values from database with specific store view
#
delete from catalog_product_entity_datetime where IFNULL(store_id, 0) <> 0 and value is null;
delete from catalog_product_entity_decimal where IFNULL(store_id, 0) <> 0 and value is null;
delete from catalog_product_entity_int where IFNULL(store_id, 0) <> 0 and value is null;
delete from catalog_product_entity_text where IFNULL(store_id, 0) <> 0 and value is null;
delete from catalog_product_entity_varchar where IFNULL(store_id, 0) <> 0 and value is null;


#
# Once you've run the queries above, you can find remaining store view-specific data using these queries.
# Results returned by these queries is store view-specfic data that is unique from the global data.
# You'll want to manually review these products in the admin to ensure nothing is awry.
#

SELECT entity_id, attribute_code
FROM (
         SELECT `eav_attribute`.`attribute_code`,
                `catalog_product_entity_decimal`.store_id,
                `catalog_product_entity_decimal`.`row_id`,
                `catalog_product_entity_decimal`.`value`,
                cpe.entity_id
         FROM catalog_product_entity_decimal
                  LEFT JOIN eav_attribute ON catalog_product_entity_decimal.attribute_id = eav_attribute.attribute_id
                  LEFT JOIN catalog_product_entity cpe on catalog_product_entity_decimal.row_id = cpe.row_id
         WHERE `store_id` <> '0'
         ORDER BY `row_id`
     ) AS a
UNION

SELECT entity_id, attribute_code
FROM (
         SELECT `eav_attribute`.`attribute_code`,
                `catalog_product_entity_int`.store_id,
                `catalog_product_entity_int`.`row_id`,
                `catalog_product_entity_int`.`value`,
                cpe.entity_id
         FROM catalog_product_entity_int
                  LEFT JOIN eav_attribute ON catalog_product_entity_int.attribute_id = eav_attribute.attribute_id
                  LEFT JOIN catalog_product_entity cpe on catalog_product_entity_int.row_id = cpe.row_id
         WHERE `store_id` <> '0'
         ORDER BY `row_id`
     ) AS a
UNION

SELECT entity_id, attribute_code
FROM (
         SELECT `eav_attribute`.`attribute_code`,
                `catalog_product_entity_text`.store_id,
                `catalog_product_entity_text`.`row_id`,
                `catalog_product_entity_text`.`value`,
                cpe.entity_id
         FROM catalog_product_entity_text
                  LEFT JOIN eav_attribute ON catalog_product_entity_text.attribute_id = eav_attribute.attribute_id
                  LEFT JOIN catalog_product_entity cpe on catalog_product_entity_text.row_id = cpe.row_id
         WHERE `store_id` <> '0'
         ORDER BY `row_id`
     ) AS a
UNION

SELECT entity_id, attribute_code
FROM (
         SELECT `eav_attribute`.`attribute_code`,
                `catalog_product_entity_varchar`.store_id,
                `catalog_product_entity_varchar`.`row_id`,
                `catalog_product_entity_varchar`.`value`,
                cpe.entity_id
         FROM catalog_product_entity_varchar
                  LEFT JOIN eav_attribute ON catalog_product_entity_varchar.attribute_id = eav_attribute.attribute_id
                  LEFT JOIN catalog_product_entity cpe on catalog_product_entity_varchar.row_id = cpe.row_id
         WHERE `store_id` <> '0'
         ORDER BY `row_id`
) AS a
ORDER BY entity_id;

#
# Find orphans attribute values from products that doesn't exist
#

SELECT DISTINCT row_id FROM catalog_product_entity_datetime WHERE NOT EXISTS(
                 SELECT catalog_product_entity.entity_id FROM catalog_product_entity
                 WHERE catalog_product_entity_datetime.row_id = catalog_product_entity.row_id
    )
UNION
SELECT DISTINCT row_id FROM catalog_product_entity_decimal WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_decimal.row_id = catalog_product_entity.row_id
    )
UNION

SELECT DISTINCT row_id FROM catalog_product_entity_int WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_int.row_id = catalog_product_entity.row_id
    )
UNION

SELECT DISTINCT row_id FROM catalog_product_entity_text WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_text.row_id = catalog_product_entity.row_id
    )
UNION

SELECT DISTINCT row_id FROM catalog_product_entity_varchar WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_varchar.row_id = catalog_product_entity.row_id
    )
UNION

SELECT DISTINCT row_id FROM catalog_product_entity_varchar WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_varchar.row_id = catalog_product_entity.row_id
    )
ORDER BY row_id;

#
# Delete orphan attribute values
#
DELETE FROM catalog_product_entity_datetime WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_datetime.row_id = catalog_product_entity.row_id
    );


DELETE FROM catalog_product_entity_decimal WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_decimal.row_id = catalog_product_entity.row_id
    );


DELETE FROM catalog_product_entity_int WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_int.row_id = catalog_product_entity.row_id
    );


DELETE FROM catalog_product_entity_text WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_text.row_id = catalog_product_entity.row_id
    );

DELETE FROM catalog_product_entity_varchar WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_varchar.row_id = catalog_product_entity.row_id
    );

DELETE FROM catalog_product_entity_varchar WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_varchar.row_id = catalog_product_entity.row_id
    );

2_category_queries.sql

/*
* Start by reviewing the comment block at the top of the product_queries.sql file above.
*
* IMPORTANT: The queries below are written for Magento Enterprise. If you're going to run them on Magento Community, you need
* to replace all instances of ".row_id" with ".entity_id". See this for context: http://magento.stackexchange.com/questions/139740/magento-2-schema-changes-for-ee-catalog-staging
*
* While Magento 2 doesn't allow you to import categories directly, it will automatically create them during product import
* if you type up the category path. If you import products and specify a value in the store_view_code column, the categories
* created during that process will also have data set at the store view-specific scope.
*/

/*
* This first set of queries just shows you what data is duplicated at specific store view scopes. The results returned by these
* queries are what are deleted by the queries further down. You can use these queries to determine if you have data you need to clean up.
*/
-- catalog_category_entity_datetime
SELECT a.*
        FROM (SELECT * FROM catalog_category_entity_datetime) AS a 
        -- This inner join finds all store view-specific rows that exactly match the global scope value. The b.store_id = 0 conditional is key as it targets the global row
        INNER JOIN (SELECT * FROM catalog_category_entity_datetime) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_category_entity_decimal
SELECT a.*
        FROM (SELECT * FROM catalog_category_entity_decimal) AS a 
        INNER JOIN (SELECT * FROM catalog_category_entity_decimal) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_category_entity_int
SELECT a.*
        FROM (SELECT * FROM catalog_category_entity_int) AS a 
        INNER JOIN (SELECT * FROM catalog_category_entity_int) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_category_entity_text
SELECT a.*
        FROM (SELECT * FROM catalog_category_entity_text) AS a 
        INNER JOIN (SELECT * FROM catalog_category_entity_text) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;
-- catalog_category_entity_varchar
SELECT a.*
        FROM (SELECT * FROM catalog_category_entity_varchar) AS a 
        INNER JOIN (SELECT * FROM catalog_category_entity_varchar) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.row_id, a.value, a.store_id;


/*
* These queries handle deleting the values duplicated at the specific store view scopes
*/
DELETE FROM `catalog_category_entity_datetime` 
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids 
    FROM (SELECT * FROM catalog_category_entity_datetime) AS a 
    INNER JOIN (SELECT * FROM catalog_category_entity_datetime) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_category_entity_decimal` 
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids 
    FROM (SELECT * FROM catalog_category_entity_decimal) AS a 
    INNER JOIN (SELECT * FROM catalog_category_entity_decimal) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_category_entity_int` 
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids 
    FROM (SELECT * FROM catalog_category_entity_int) AS a 
    INNER JOIN (SELECT * FROM catalog_category_entity_int) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_category_entity_text` 
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids 
    FROM (SELECT * FROM catalog_category_entity_text) AS a 
    INNER JOIN (SELECT * FROM catalog_category_entity_text) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);
DELETE FROM `catalog_category_entity_varchar` 
WHERE `value_id` IN (
    SELECT a.value_id AS value_ids 
    FROM (SELECT * FROM catalog_category_entity_varchar) AS a 
    INNER JOIN (SELECT * FROM catalog_category_entity_varchar) AS b ON a.attribute_id = b.attribute_id AND a.row_id = b.row_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.row_id, a.value, a.store_id
);

/*
* Once you've run the queries above, you can find remaining store view-specific data using these queries.
* Results returned by these queries is store view-specfic data that is unique from the global data.
* You'll want to manually review these categories in the admin to ensure nothing is awry.
*/
SELECT `catalog_category_entity_datetime`.* FROM catalog_category_entity_datetime WHERE `store_id` <> '0';
SELECT `catalog_category_entity_decimal`.* FROM catalog_category_entity_decimal WHERE `store_id` <> '0';
SELECT `catalog_category_entity_int`.* FROM catalog_category_entity_int WHERE `store_id` <> '0';
SELECT `catalog_category_entity_text`.* FROM catalog_category_entity_text WHERE `store_id` <> '0';
SELECT `catalog_category_entity_varchar`.* FROM catalog_category_entity_varchar WHERE `store_id` <> '0';