Sort Magento categories alphabetically using SQL query but for specific root category

Question

I found this SQL query (Magento Sort Categories Alphabetically using SQL Query) and I've been successfully using it to re-sort all of our 30000+ categories but the problem I have is that it re-sorts all categories. I only have one category that needs re-sorting every now and then so my question is, how do I modify below query so that it only sorts all subcategories for a specific category (ID: 38744)?

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 = 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 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;

Answer 1

When you are creating the table with the join, you can add a condition to the catalog_category_entity table that specifies what the path looks like. The first one you create would be 1/2/%, after that, it depends on the order which you created the categories, so you will need to look at the path value for the root category that you are looking for:

select path from catalog_category_entity where entity_id = [the id of your category];

In the case that you want to filter by the first root category, it would look like this (note the where condition in the second to last line):

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 = 41
WHERE e.path like '1/2/%'
ORDER BY vn.value;

To resolve the null value issue, try the where condition below. Note that the position column is on the aaa_new_position table

UPDATE catalog_category_entity e
        LEFT JOIN
    AAA_NEW_POSITION np ON e.entity_id = np.entity_id 
SET 
    e.position = np.new_position
WHERE np.new_position IS NOT NULL;