Finds all the overridden values in storeview scopes for Magento 1 categories

magento1_find_category_scope_overrides.sql

-- Find overridden values

SELECT a.attribute_code, overrides.* FROM (
SELECT 'datetime', entity_id, store_id, attribute_id, `value` FROM catalog_category_entity_datetime WHERE store_id!=0 UNION ALL
SELECT 'decimal', entity_id, store_id, attribute_id, `value` FROM catalog_category_entity_decimal WHERE store_id!=0 UNION ALL
SELECT 'int', entity_id, store_id, attribute_id, `value` FROM catalog_category_entity_int WHERE store_id!=0 UNION ALL
SELECT 'text', entity_id, store_id, attribute_id, `value` FROM catalog_category_entity_text WHERE store_id!=0 UNION ALL
SELECT 'varchar', entity_id, store_id, attribute_id, `value` FROM catalog_category_entity_varchar WHERE store_id!=0
) AS overrides
LEFT JOIN eav_attribute  a ON overrides.attribute_id=a.attribute_id
WHERE store_id=3 AND entity_id IN(76,24,4,61,63,26,39,40,41,9,5,80,47,19,74,38,86,78,10,73,15,33,21,87,36,81,34,49,51,53,55,56,57,77,79);

-- Delete global values for an attribute, and promote scoped values to be global.
DELETE FROM catalog_category_entity_varchar 
WHERE  value_id IN(SELECT value_id 
                   FROM   (SELECT global.value_id 
                           FROM   catalog_category_entity_varchar AS `global` 
                                  LEFT JOIN (SELECT value_id, 
                                                    'varchar', 
                                                    entity_id, 
                                                    store_id, 
                                                    attribute_id, 
                                                    `VALUE` 
                                             FROM   catalog_category_entity_varchar 
                                             WHERE  store_id = 3 
                                                AND attribute_id = 133) AS scoped 
                                         ON scoped.attribute_id = global.attribute_id 
                                            AND scoped.entity_id = global.entity_id 
                           WHERE  global.store_id = 0 
                              AND scoped.value_id IS NOT NULL) AS derived); 
                             
UPDATE catalog_category_entity_varchar SET store_id=0 WHERE store_id=3 AND attribute_id=133;