Geek Answers Handbook

MySQL Order, then Upgrade Inner Join

I have a table categoriesthat contains the column IDs and ParentID. I would like to add a field called "Level", which indicates which level in the category tree of each category. I think I found my solution, but it is in sql not mysql. So I converted it to the correct syntax. However, I think they lack a step. So here is my code:

 ALTER TABLE categories DROP Level;

 ALTER TABLE categories ADD Level INT NULL;

 UPDATE categories 
SET Level = 0
WHERE ParentID IS NULL;

 UPDATE categories AS A
    INNER JOIN categories B ON A.ParentID = B.ID
    SET A.Level = B.Level + 1
 WHERE A.Level IS NULL AND
          B.Level IS NOT NULL;

I think the problem may be that in my DB. The order of categories does not come in any particular order, which I mean:

ID  ParentID
2   NULL 0
4   55
7   2
.....more categories
55  2

So I would like him to do this:

ID  Parent  Level
2   NULL    0
3   55      2
7   2       1
....
55  2       1

However, I think, but I could be wrong, this is what I need to either order ParentID first before I do the last operation, or my request will be skipped.

, , , , ;

ID  Parent  Level
2   NULL    0
3   55      NULL
7   2       1
....
55  2       1

?

+4
1

, ? 3 , ,

ALTER TABLE categories DROP Level;
ALTER TABLE categories ADD Level INT NULL;

UPDATE categories SET Level = 0 WHERE ParentID IS NULL;
UPDATE categories SET level = 1 where parentID = 2; 
UPDATE categories SET level = 2 where parentID > 2;
0

More articles:


All Articles