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

Question

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?

Have you looked at the CASE statement? <softwaretestinghelp.com/mysql-case-statement/#MySQL_CASE_Syntax> — SikorskyS60
– SikorskyS60, Commented Apr 13, 2022 at 10:14
I want to add a column named tenant - your desired results have a column permissions. What happened to Tenant? — Stu
– Stu, Commented Apr 13, 2022 at 10:21

Stu · Accepted Answer · 2022-04-13 10:21:24Z

2

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);

answered Apr 13, 2022 at 10:21

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related