How to copy multiple rows but change one field with mysql

Say I have a table such as:

val cols cole
1   1    3
2   1    5
4   1    9
2   6    8
3   1    9
1   4    9
2   9    9

I want a query that duplicates all rows where val=2 but change val to 5 for these new rows.

Resulting in:

val cols cole
1   1    3
2   1    5
4   1    9
2   6    8
3   1    9
1   4    9
2   9    9
5   1    5
5   6    8
5   9  开发者_运维百科  9

Do you want a SELECT query, or an INSERT?

Here's a SELECT that should work for you:

select val, cols, cole
from your_table
UNION ALL
select 5 as val, cols, cole
from your_table
where val = 2

Here's an INSERT:

insert into your_table (val, cols, cole)
select 5 as val, cols, cole
from your_table
where val = 2