Magento – Sort Categories Alphabetically Using SQL Query

catalogdatabasemagento-1.9MySQLsql

The below sql query isn't working for some reason, has anyone got any ideas what is wrong?

SET @i=0;
SET @j=0;
DROP TABLE IF EXISTS AAA_NEW_POSITION;
CREATE TABLE AAA_NEW_POSITION
        SELECT e.entity_id AS 'entity_id', 
                  vn.value AS 'name',
                  e.position AS 'old_position', @i:=@i+1 AS 'new_position'
                FROM 
                        catalog_category_entity e 
                        LEFT JOIN catalog_category_entity_varchar vn 
                                ON e.entity_id = vn.entity_id AND
                                vn.attribute_id = 71
                ORDER BY vn.value;

ALTER TABLE AAA_NEW_POSITION ORDER BY name;
UPDATE AAA_NEW_POSITION SET new_position= @j:=@j+1 ORDER BY name;

UPDATE
                catalog_category_entity e
                LEFT JOIN AAA_NEW_POSITION np
                    ON e.entity_id = np.entity_id
        SET
                e.position = np.new_position;

DROP TABLE IF EXISTS AAA_NEW_POSITION;

I get the following error 

#1146 - Table 'printtes1_j5wj.catalog_category_entity' doesn't exist

Any ideas? the actual table name is: wprt_catalog_category_entity_varchar

Best Answer

wprt_ is my table prefix, attribute_id = 41 is the name attribute found in the eav_attribute table

SET @i=0;
SET @j=0;
DROP TABLE IF EXISTS AAA_NEW_POSITION;
CREATE TABLE AAA_NEW_POSITION
        SELECT e.entity_id AS 'entity_id', 
                  vn.value AS 'name',
                  e.position AS 'old_position', @i:=@i+1 AS 'new_position'
                FROM 
                        wprt_catalog_category_entity e 
                        LEFT JOIN wprt_catalog_category_entity_varchar vn 
                                ON e.entity_id = vn.entity_id AND
                                vn.attribute_id = 41
                ORDER BY vn.value;

ALTER TABLE AAA_NEW_POSITION ORDER BY name;
UPDATE AAA_NEW_POSITION SET new_position= @j:=@j+1 ORDER BY name;

UPDATE
                wprt_catalog_category_entity e
                LEFT JOIN AAA_NEW_POSITION np
                    ON e.entity_id = np.entity_id
        SET
                e.position = np.new_position;

DROP TABLE IF EXISTS AAA_NEW_POSITION;
Related Topic