1

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

2

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;
  • Can't yet, have to wait 19 hours :) thanks – ChocolateTeapot Feb 15 '15 at 16:50
1

Did you enable the table prefix option when installing Magento? If so you need to use the full table name - e.g. the actual name you specified. Other idea might be that you need to run on a correct database executing USE databasename query before other SQL statements

  • Hi, there is a table prefix wprt_ I have tried adding in wprt_catalog_category_entity_varchar in to the above but I get the same error, should I be using the wprt_ bit anywhere else in the code? – ChocolateTeapot Feb 15 '15 at 13:13
  • Actually, sorted it, I was missing a wprt_ from one of the options above, also the attribute_id was incorrect. – ChocolateTeapot Feb 15 '15 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.