Mysql - Create a new row in table for each distinct value in a column in the same table

user_1399145 0
Viewed 586

I have searched but not quite found what I am looking for. I need to create a new entry in a table for each existing entry with a unique id. For example - my table is like this.

 id   userid    action
 ---  --------  ----------
 1    55        Red
 2    55        Blue
 3    56        Red
 4    56        Blue

For each unique Userid, I need to add another entry - for example

     id   userid    action
     ---  --------  ----------
     1    55        Red
     2    55        Blue
     3    56        Red
     4    56        Blue 
     5    55        Green
     6    56        Green

Is this possible??

sql-insert select mysql sql
1 Answers

You can use the insert ... select syntax to generate a new record for each distinct userid:

insert into mytable(userid, action)
select distinct userid, 'Green' from mytable
user_106767160
Related