How to Duplicate Rows in MySQL

To duplicate rows in MySQL, we can combine the INSERT and SELECT statements. Here's the syntax:

INSERT INTO
    table (column1, column2, columnN)
SELECT
    column1, column2, columnN
FROM table
WHERE condition -- optional

Suppose we have a comments table with the following data:

SELECT * FROM comments;
-- +----+------+-----------------------+
-- | id | name | text                  |
-- +----+------------------------------+
-- |  1 | Nunu | Nice article!         |
-- |  2 | Vivi | That's awesome!       |
-- |  3 | Ian  | Never heard about it  |
-- +----+------------------+-----------+

Then, we want to duplicate the rows where name equals Ian. Here's the query:

INSERT INTO
    comments (name, text)
SELECT
    name, text
FROM comments
WHERE name = 'Ian';

Here's the comments data after executing the query:

SELECT * FROM comments;
-- +----+------+-----------------------+
-- | id | name | text                  |
-- +----+------+-----------------------+
-- |  1 | Nunu | Nice article!         |
-- |  2 | Vivi | That's awesome!       |
-- |  3 | Ian  | Never heard about it  |
-- |  4 | Ian  | Never heard about it  |
-- +----+------+-----------+-----------+

If we want to duplicate the rows and add custom values, we can include those values directly in the SELECT statement. For example:

INSERT INTO
    comments (name, text, is_duplicated, duplicated_by)
SELECT
    name, text, true, 'system'
FROM comments
WHERE name = 'Ian';

Here's the comments data after executing the query:

SELECT * FROM comments;
-- +----+------+-----------------------+---------------+---------------+
-- | id | name | text                  | is_duplicated | duplicated_by |
-- +----+------+-----------------------+---------------+---------------+
-- |  1 | Nunu | Nice article!         |             0 | NULL          |
-- |  2 | Vivi | That's awesome!       |             0 | NULL          |
-- |  3 | Ian  | Never heard about it  |             0 | NULL          |
-- |  4 | Ian  | Never heard about it  |             0 | NULL          |
-- |  5 | Ian  | Never heard about it  |             1 | system        |
-- |  6 | Ian  | Never heard about it  |             1 | system        |
-- +----+------+-----------------------+---------------+---------------+

If we want to change the value of a column in the duplicated rows, we can do it directly in the SELECT statement. For example:

INSERT INTO
    comments (name, text, is_duplicated, duplicated_by)
SELECT
    name, CONCAT(text, ' - duplicate'), true, 'system'
FROM comments
WHERE name = 'Ian';

Here's the comments data after executing the query:

SELECT * FROM comments;
-- +----+------+----------------------------------+---------------+---------------+
-- | id | name | text                             | is_duplicated | duplicated_by |
-- +----+------+----------------------------------+---------------+---------------+
-- |  1 | Nunu | Nice article!                    |             0 | NULL          |
-- |  2 | Vivi | That's awesome!                  |             0 | NULL          |
-- |  3 | Ian  | Never heard about it             |             0 | NULL          |
-- |  4 | Ian  | Never heard about it             |             0 | NULL          |
-- |  5 | Ian  | Never heard about it             |             1 | system        |
-- |  6 | Ian  | Never heard about it             |             1 | system        |
-- |  7 | Ian  | Never heard about it - duplicate |             1 | system        |
-- |  8 | Ian  | Never heard about it - duplicate |             1 | system        |
-- |  9 | Ian  | Never heard about it - duplicate |             1 | system        |
-- | 10 | Ian  | Never heard about it - duplicate |             1 | system        |
-- +----+------+----------------------------------+---------------+---------------+

---

That's it — how to duplicate rows in MySQL, along with some examples. Hope it helps!