Updating to every number after column value, up to 9?

Question

So I created a database table in MySQL that held permission rights for permissions and commands, the command rights started with the prefix command_ in the column permission_name and then I have an extra column called allowed_ranks, which is a list of INT rank ID's that are required, split by a , character.

The issue is, the command ones were anything higher, and I've put 1 id in allowed_ranks, is there a way I can loop through all the ones with column starting with command_ and change the allowed_ranks that are just 1 ID to every number starting from that to 9? 9 is the maximum rank ID.

I've already done part of the query, I'm just not sure how to do the updating?

UPDATE permission_rights` SET `allowed_ranks` = '?' WHERE `permission_name` LIKE 'command_%';

How would I update it to every number after the columns value up to 9? So lets say I had this record... just a quick example to ensure you know what I mean.

 | permission_name | allowed_ids |
----------------------------------
 | command_hello   | 2
 | command_junk    | 5
 | command_delete  | 8 
 | command_update  | 1

Would become...

 | permission_name | allowed_ids |
----------------------------------
 | command_hello   | 2,3,4,5,6,7,8,9
 | command_junk    | 5,6,7,8,9
 | command_delete  | 8,9
 | command_update  | 1,2,3,4,5,6,7,8,9

can you post you DB structure with sample data? it's not clear where the list of allowed_ids comes from — Yossi Vainshtein, Dec 10 '17 at 17:37

score 0 · Answer 1 · answered Dec 10 '17 at 18:13

The better approach would be to use a number generator (some method which will produce number from 1 to n), but general MySQL has no such capability.

If you use MariaDB you can use seq_1_to_1000 as suggested here in Answer by O.Jones.

However your use case seems to be simpler, since you said that the highest rank is 9, I would just use

update a 
set a.allowed_ids = RIGHT('1,2,3,4,5,6,7,8,9',19-2*a.allowed_ids) 
where a.permission_name like 'command_%'

Updating to every number after column value, up to 9?

1 Answers1