CODEWITHSUNDEEP
mysql
Ctfrancia
Ctfrancia

Reputation: 1588

mysql - create new column and conditionally populate the new column with data

I am trying to alter a table to add an additional column and populate the new column based on another column. For example I have a table that looks like this:

+----+------------+----------+
| id | username   | role     |
+----+------------+----------+
| 1  |    foo     | admin    |
+----+------------+----------+
| 2  |    bar     | operator |
+----+------------+----------+

I want to add a column named tenant and based on the value in role column populate the value inside tenent:

+----+------------+----------+--------------+
| id | username   | role     | permissions  |
+----+------------+----------+--------------+
|  1 |    foo     | admin    |      *       |
+----+------------+----------+--------------+
|  2 |    bar     | operator |   limited    |
+----+------------+----------+--------------+

Is there a MySQL query that can be performed or will I need to create a script to do this?

Upvotes: 0

Views: 37

Answers (1)

Stu
Stu

Reputation: 32599

Consider using a generated column, something like the following. That way, when you update the role, the Permissions will automatically be correct:

alter table MyTable add column Permissions varchar(10) generated always as 
  (case when role='admin' then '*' else 'limited' end);

Upvotes: 2

Related Questions