Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL – Modifiy table field based on another table field

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;

Here is the table create query :

CREATE TABLE `Rights` (
  `rightId` int NOT NULL AUTO_INCREMENT,
  `RightName` varchar(255) NOT NULL,
  `RightLibelle` text,
  `ParentRightId` int DEFAULT NULL,
  `RightIsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`rightId`),
  KEY `ParentRightId` (`ParentRightId`),
  CONSTRAINT `Rights_ibfk_1` FOREIGN KEY (`ParentRightId`) REFERENCES `Rights` (`rightId`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And a snippet of code to create example data :

INSERT INTO `Rights` VALUES 
 (1,'CAROUSEL MENU',NULL,NULL,1)
,(2,'CAROUSEL MENU_SALES',NULL,NULL,1)
,(3,'CAROUSEL MENU_SALES_CONFIGURATOR',NULL,NULL,1)
;

Thanks !

Tags:

2

Answers


  1. 0

    As we can see in your data example, we can self join table Rights where RightName of parent right is substring of RightName of child right.
    Then take the longest of them.

    See example

    UPDATE Rights AS trg
JOIN ( 
  select t1.rightId, t2.rightId parentId
    ,row_number()over(partition by t1.rightId order by length(t2.RightName) desc)rn
  from Rights t1
  left join Rights t2 on t1.rightId<>t2.rightId 
    and substring(t1.RightName,1,length(t2.RightName))=t2.RightName
)src on src.rightId=trg.rightId
  SET trg.ParentRightId = src.parentId
where rn=1;

    Result is

    rightId RightName RightLibelle ParentRightId RightIsActive
    1 CAROUSEL MENU null null 1
    2 CAROUSEL MENU_SALES null 1 1
    3 CAROUSEL MENU_SALES_CONFIGURATOR null 2 1

    Source for UPDATE

    rightId RightName ParentRightId RightIsActive parentId RightName rn
    1 CAROUSEL MENU null 1 null null 1
    2 CAROUSEL MENU_SALES null 1 1 CAROUSEL MENU 1
    3 CAROUSEL MENU_SALES_CONFIGURATOR null 1 2 CAROUSEL MENU_SALES 1
    3 CAROUSEL MENU_SALES_CONFIGURATOR null 1 1 CAROUSEL MENU 2

    Demo

    Login or Signup to reply.
  2. 0

    If I understand your question, I think you really want the following SQL:

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

    If each RightName contains 3 levels (grandparent_parent_child), then the string from ‘1_2_3’ that will be in its parent RightName will be ‘1_2’. For example, the parent RightName could be ‘9_1_2’. So from the record with RightName ‘1_2_3’ you need to compare the first three characters to the last three characters of the RightName of its parent. The -1 value in your first SUBSTRING_INDEX() call will give you only ‘3’.

    From https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index
    Regarding the third parameter of this function, "If count is positive, everything to the left of the final delimiter is returned. If count is negative, everything to the right of the final delimiter is returned."

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search