I have a SQL table named Rights with the following fields: rightId, RightName, ParentRightId, RightIsActive.

The RightName field contains the name of each right, including its parent rights separated by underscores.

For example, if right 3 is a child of right 2, and right 2 is a child of right 1, then the name of right 3 would be ‘1_2_3’.

I want the ParentRightId field to hold the value of the parent’s rightId based on its name. How can I achieve this ?

I already tried this code and didn’t managed to make it work :

UPDATE Rights AS r1
JOIN Rights AS r2 ON SUBSTRING_INDEX(r1.RightName, '_', -1) = SUBSTRING_INDEX(r2.RightName, '_', 1)
SET r1.ParentRightId = r2.rightId;

Thanks !