Introduction

The standard MySQL way to copy rows while changing some values is INSERT ... SELECT. You select existing data from one or more rows, replace the fields you want to change with literals or expressions, and insert the result as new rows.

Basic Pattern

Suppose you have an orders table and want to copy one order while resetting its status and timestamp. The pattern looks like this:

1INSERT INTO orders (
2    customer_id,
3    product_id,
4    quantity,
5    status,
6    created_at
7)
8SELECT
9    customer_id,
10    product_id,
11    quantity,
12    'pending',
13    NOW()
14FROM orders
15WHERE id = 42;

The important detail is that you list the destination columns explicitly. You usually leave out the auto-increment primary key so MySQL can generate a new one.

Copy Multiple Rows with Changes

You are not limited to a single source row. INSERT ... SELECT can duplicate many rows at once.

1INSERT INTO tasks (
2    project_id,
3    title,
4    due_date,
5    priority,
6    version
7)
8SELECT
9    project_id,
10    title,
11    DATE_ADD(due_date, INTERVAL 7 DAY),
12    'low',
13    version + 1
14FROM tasks
15WHERE project_id = 10;

Here the copied rows keep most of their original values, but:

• the due date moves forward by seven days
• the priority is reset
• the version number is incremented

This is much cleaner than selecting rows into application code and re-inserting them one at a time.

Copy Between Tables

The same technique works across different tables as long as the selected columns match the insert target.

1INSERT INTO archived_orders (
2    original_order_id,
3    customer_id,
4    total_amount,
5    archived_at
6)
7SELECT
8    id,
9    customer_id,
10    total_amount,
11    NOW()
12FROM orders
13WHERE status = 'completed';

The source and destination do not have to share the same schema exactly. You can reshape the result as part of the SELECT.

Why Explicit Column Lists Matter

Never rely on INSERT INTO table SELECT * FROM table ... for this kind of operation. It is fragile because:

• column order can change later
• generated columns may not behave the way you expect
• primary keys and unique columns often need special handling

Explicit column lists make the copy safer and easier to review.

Think About Constraints Before Running the Query

Copying rows can fail if the new rows violate:

• primary key uniqueness
• unique indexes
• foreign key constraints
• application-level assumptions about timestamps or status values

For example, if the table has a unique slug column, copying a row without changing that field will fail. In that case, change the duplicated value in the SELECT:

1INSERT INTO articles (title, slug, body)
2SELECT
3    title,
4    CONCAT(slug, '-copy'),
5    body
6FROM articles
7WHERE id = 7;

Use Transactions for Safety

When the copy is part of a larger business operation, wrap it in a transaction.

1START TRANSACTION;
2
3INSERT INTO orders (
4    customer_id,
5    product_id,
6    quantity,
7    status,
8    created_at
9)
10SELECT
11    customer_id,
12    product_id,
13    quantity,
14    'pending',
15    NOW()
16FROM orders
17WHERE id = 42;
18
19COMMIT;

That makes it easier to roll back if later steps fail.

Common Pitfalls

• Copying the primary key column and causing a duplicate-key error.
• Using SELECT * instead of listing columns explicitly.
• Forgetting to modify unique fields that must stay distinct.
• Copying rows from the same table without a restrictive WHERE clause and creating far more duplicates than intended.
• Running a large duplication query outside a transaction when related writes must stay consistent.

Summary

• Use INSERT ... SELECT to copy rows while changing selected fields.
• Omit auto-increment keys unless you intentionally want to control them.
• Replace fields in the SELECT with literals, expressions, or MySQL functions.
• Always list destination columns explicitly.
• Check uniqueness and constraint rules before duplicating data.