current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

2 Answers 2

up vote 1 down vote

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

share|improve this answer
    
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 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 at 13:37
up vote 1 down vote accepted

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;
share|improve this answer
    
If this solution is working for you then please accept your own answer. – MagePsycho Feb 15 at 13:43
    
Can't yet, have to wait 19 hours :) thanks – ChocolateTeapot Feb 15 at 16:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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