Duplicating and replacing field values on rows in MySQL table

Not sure if this is possible in MySQL but I am hoping for some guidance or direction on the following.

Say I have a table as such

Col_1 Col_2 col_3
AAA   56    3
AAA   99    3
AAA   100   3
BBB   43    1
BBB   66    1
CCC   33    0

and I want to duplicate rows where Col_1=AAA, but replace "AAA" with "DDD" and set col_3 to 0. i.e. after performing the appropriate command the table looks like

Col_1 Col_2 col_3
AAA   56    3
AAA   99    3
AAA   100   3
BBB   43    1
BBB   66    1
CCC   33    0
DDD   56    0
DDD   99    0
DDD   100   0

Doing some reading it appears "Select ... for update" may be a starting point but am relatively clueless how to construct such a statement that fits this scenario.

Any advice is greatly appreciated! thanks.

Note: Even though Col_2 is the only column I showed in this example that is left unaltered, in reality my problem has about 10 unaltered columns, but I have shown just one for simplicity; point is, a solution shouldn't be discussed that explicitly focus' on just one column being unaltered... hope that is clea开发者_如何学Gor.

---

Try using the UNION clause:

SELECT Col_1, Col_2, Col_3 FROM tablename
UNION
SELECT 'DDD' AS 'Col_1', Col_2, '0' AS Col_3
FROM tablename
WHERE Col_1 = 'AAA';