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

Question

In MySQL I am trying to copy a row with an autoincrement column ID 1 and insert the data into same table as a new row with column ID 2   How can I do this in a single query

User · Answer

For a quick  clean solution that doesn t require you to name columns  you can use a prepared statement as described here  https   stackoverflow com a 23964285 292677  If you need a complex solution so you can do this often  you can use this procedure   DELIMITER     CREATE PROCEDURE  duplicateRows   schemaName text   tableName text   whereClause text   omitColumns text  SQL SECURITY INVOKER BEGIN   SELECT IF TRIM  omitColumns   lt  gt      CONCAT  id        TRIM  omitColumns     id   INTO  omitColumns     SELECT GROUP CONCAT COLUMN NAME  FROM information schema columns    WHERE table schema    schemaName AND table name    tableName AND FIND IN SET COLUMN NAME  omitColumns    0 ORDER BY ORDINAL POSITION INTO  columns     SET  sql   CONCAT  INSERT INTO     tableName        columns          SELECT     columns       FROM     schemaName        tableName         whereClause      PREPARE stmt1 FROM  sql    EXECUTE stmt1  END   You can run it with   CALL duplicateRows  database    table    WHERE condition   optional    omit columns optional      Examples  duplicateRows  acl    users    WHERE id   200    -- will duplicate the row for the user with id 200 duplicateRows  acl    users    WHERE id   200    created ts    -- same as above but will not copy the created ts column value     duplicateRows  acl    users    WHERE id   200    created ts updated ts    -- same as above but also omits the updated ts column duplicateRows  acl    users    -- will duplicate all records in the table   DISCLAIMER  This solution is only for someone who will be repeatedly duplicating rows in many tables  often  It could be dangerous in the hands of a rogue user

User · Answer

You can also pass in  0  as the value for the column to auto-increment  the correct value will be used when the record is created  This is so much easier than temporary tables   Source  Copying rows in MySQL   see the second comment  by TRiG  to the first solution  by Lore

User · Answer

I was looking for the same feature but I don t use MySQL  I wanted to copy ALL the fields except of course the primary key  id   This was a one shot query  not to be used in any script or code   I found my way around with PL SQL but I m sure any other SQL IDE would do  I did a basic  SELECT    FROM mytable  WHERE id 42    Then export it to a SQL file where I could find the   INSERT INTO table  col1  col2  col3        col42   VALUES  1  2  3       42     I just edited it and used it    INSERT INTO table  col1  col2  col3        col42   VALUES  mysequence nextval  2  3       42

User · Answer

IMO  the best seems to use sql statements only to copy that row  while at the same time only referencing the columns you must and want to change  CREATE TEMPORARY TABLE temp table ENGINE MEMORY  SELECT   FROM your table WHERE id 1  UPDATE temp table SET id 0     Update other values at will      INSERT INTO your table SELECT   FROM temp table  DROP TABLE temp table   See also av8n com - How to Clone an SQL Record Benefits   The SQL statements 2 mention only the fields that need to be changed during the cloning process  They do not know about     or care about     other fields  The other fields just go along for the ride  unchanged  This makes the SQL statements easier to write  easier to read  easier to maintain  and more extensible  Only ordinary MySQL statements are used  No other tools or programming languages are required  A fully-correct record is inserted in your table in one atomic operation

User · Answer

If you re able to use MySQL Workbench  you can do this by right-clicking the row and selecting  Copy row   and then right-clicking the empty row and selecting  Paste row   and then changing the ID  and then clicking  Apply    Copy the row     Paste the copied row into the blank row     Change the ID     Apply

User · Answer

INSERT INTO  dbMyDataBase   tblMyTable          IdAutoincrement         Column2         Column3         Column4       SELECT      NULL         Column2         Column3         CustomValue  AS Column4  FROM  dbMyDataBase   tblMyTable   WHERE  tblMyTable   Column2     UniqueValueOfTheKey         mySQL 5 6

User · Answer

A lot of great answers here  Below is a sample of the stored procedure that I wrote to accomplish this task for a Web App that I am developing    -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements  SET NOCOUNT ON  -- Create Temporary Table SELECT   INTO  tempTable FROM  lt YourTable gt  WHERE Id   Id  --To trigger the auto increment UPDATE  tempTable SET Id   NULL   --Update new data row in  tempTable here   --Insert duplicate row with modified data back into your table INSERT INTO  lt YourTable gt  SELECT   FROM  tempTable  -- Drop Temporary Table DROP TABLE  tempTable

User · Answer

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

User · Answer

Try this  INSERT INTO test table  SELECT null txt FROM test table   Every time you run this query  This will insert all the rows again with new ids  values in your table and will increase exponentially  I used a table with two columns i e id and txt and id is auto increment

User · Answer

insert into MyTable field1  field2  id backup      select field1  field2  uniqueId from MyTable where uniqueId    Id

User · Answer

This helped and it supports a BLOB TEXT columns   CREATE TEMPORARY TABLE temp table AS SELECT   FROM source table WHERE id 2  UPDATE temp table SET id NULL WHERE id 2  INSERT INTO source table SELECT   FROM temp table  DROP TEMPORARY TABLE temp table  USE source table

User · Answer

Dump the row you want to sql and then use the generated SQL  less the ID column to import it back in

User · Answer

I tend to use a variation of what mu is too short posted   INSERT INTO something log SELECT NULL  s   FROM something AS s WHERE s id   1    As long as the tables have identical fields  excepting the auto increment on the log table   then this works nicely   Since I use stored procedures whenever possible  to make life easier on other programmers who aren t too familiar with databases   this solves the problem of having to go back and update procedures every time you add a new field to a table   It also ensures that if you add new fields to a table they will start appearing in the log table immediately without having to update your database queries  unless of course you have some that set a field explicitly   Warning  You will want to make sure to add any new fields to both tables at the same time so that the field order stays the same    otherwise you will start getting odd bugs  If you are the only one that writes database interfaces AND you are very careful then this works nicely  Otherwise  stick to naming all of your fields   Note  On second thought  unless you are working on a solo project that you are sure won t have others working on it stick to listing all field names explicitly and update your log statements as your schema changes  This shortcut probably is not worth the long term headache it can cause    especially on a production system

User · Answer

Say the table is user id  user name  user email    You can use this query   INSERT INTO user  SELECT NULL user name  user email FROM user WHERE id   1

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

Examples related to mysql

Examples related to sql

Examples related to insert

Examples related to copy