Copy and Insert to Same Table No Duplication and With Minor Changes to Value

How to copy a row and insert in same table with a autoincrement field in MySQL?

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

How to duplicate records, modify and add them to same table

I would suggest just using a view instead of trying to create and maintain two copies of the same data. Then you just select from the view instead of the base table.

create view MyReversedDataView as

select ID
    , Col1
    , Col2
from MyTable

UNION ALL

select ID
    , Col2
    , Col1
from MyTable

MySQL: How to copy rows, but change a few fields?

INSERT INTO Table
          ( Event_ID
          , col2
           ...
          )
     SELECT "155"
          , col2
           ...
      FROM Table WHERE Event_ID = "120"

Here, the col2, ... represent the remaining columns (the ones other than Event_ID) in your table.

Duplicate / Copy records in the same MySQL table

The way that I usually go about it is using a temporary table. It's probably not computationally efficient but it seems to work ok! Here i am duplicating record 99 in its entirety, creating record 100.

CREATE TEMPORARY TABLE tmp SELECT * FROM invoices WHERE id = 99;

UPDATE tmp SET id=100 WHERE id = 99;

INSERT INTO invoices SELECT * FROM tmp WHERE id = 100;

Hope that works ok for you!

SQL Insert existing/duplicate row into table but change only one column value?

No. There is no way to say * except column_foo in SQL.

The workaround would be to generate the

SELECT
  col1
, col2
, [...]
, coln
FROM foo;

statement (or parts of it) by querying the database's system catalogue for the column names in their order. There is always a table with all tables and a table with all columns.
Then, make sure you put the necessary commas in the right place (or remove them where you don't need them, or generate the comma in all rows of the report but the first - by using the ROW_NUMBER() OLAP function and evaluating whether it returns 1 or something else). Finally, edit the right date column, by replacing it with CURRENT_DATE or whatever your database uses for the current day.

Good luck -
Marco

PHP MySQL Copy a row within the same table... with a Primary and Unique key

Select all columns explicitly, except the id column:

INSERT INTO items
(col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM items
WHERE id = '9198'

Your next question will probably be:

Is there a way to do this without listing all the columns explicitly?

Answer: No, I don't think so.

---

Related Topics