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

1_product_queries.sql

#
# These queries check attributes values at the none-global store view scopes.
#
-- 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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
WHERE a.store_id <> 0
GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_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.entity_id = cpe.entity_id
         WHERE store_id <> '0'
         ORDER BY entity_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.entity_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.entity_id = cpe.entity_id
         WHERE store_id <> '0'
         ORDER BY entity_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.entity_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.entity_id = cpe.entity_id
         WHERE store_id <> '0'
         ORDER BY entity_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.entity_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.entity_id = cpe.entity_id
         WHERE store_id <> '0'
         ORDER BY entity_id
) AS a
ORDER BY entity_id;

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

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

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

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

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

SELECT DISTINCT entity_id FROM catalog_product_entity_varchar WHERE NOT EXISTS(
        SELECT catalog_product_entity.entity_id FROM catalog_product_entity
        WHERE catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id
    )
ORDER BY entity_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.entity_id = catalog_product_entity.entity_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.entity_id = catalog_product_entity.entity_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.entity_id = catalog_product_entity.entity_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.entity_id = catalog_product_entity.entity_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.entity_id = catalog_product_entity.entity_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.entity_id = catalog_product_entity.entity_id
    );

2_category_queries.sql

/*
* 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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
        WHERE a.store_id <> 0
        GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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.entity_id = b.entity_id AND a.value = b.value AND b.store_id = 0
    WHERE a.store_id <> 0
    GROUP BY a.attribute_id, a.entity_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';