Copy row but with new id

Question

I have a table  test  with an auto incremented id and an arbitrary number of columns   I want to make a copy of a row in this table with all columns the same except for the id of course    Is there a way to do this without naming all columns    I thought INSERT    SELECT    ON DUPLICATE KEY would help me until I realised that it never makes an INSERT ON DUPLICATE  it just updates the existing row

User · Answer

This works in MySQL all versions and Amazon RDS Aurora:

INSERT INTO my_table SELECT 0,tmp.* FROM tmp;

or

Setting the index column to NULL and then doing the INSERT.

But not in MariaDB, I tested version 10.

User · Answer

SET  table    the table   SELECT GROUP CONCAT IF COLUMN NAME IN   id    0  CONCAT  quot    quot   COLUMN NAME   quot    quot     FROM INFORMATION SCHEMA COLUMNS                   WHERE TABLE SCHEMA   DATABASE   AND TABLE NAME    table INTO  columns  SET  s   CONCAT  INSERT INTO     table    SELECT     columns   FROM     table    WHERE id 1    PREPARE stmt FROM  s  EXECUTE stmt

User · Answer

depending on how many columns there are  you could just name the columns  sans the ID  and manually add an ID or  if it s in your table  a secondary ID  sid    insert into PROG date  level  Percent  sid  select  date  level  Percent  55 from PROG where sid   31 Here  if sid 31 has more than one resultant row  all of them will be copied over to sid 55 and your auto iDs will still get auto-generated  for ID only  insert into PROG date  level  Percent  ID  select  date  level  Percent  55 from PROG where ID   31  where 55 is the next available ID in the table and ID 31 is the one you want to copy

User · Answer

INSERT into table name          product id         other products url id         brand         title         price         category         sub category         quantity         buy now         buy now url         is available         description         image url         image type         server image url         reviews         hits         rating         seller name         seller desc         created on         modified on         status    SELECT       product id         other products url id         brand         title         price         category         sub category         quantity         buy now        concat  buy now url       test123456          is available         description         image url         image type         server image url         reviews         hits         rating         seller name         seller desc         created on         modified on         status   FROM  table name  WHERE id  YourRowID

User · Answer

Let us say your table has following fields       pk id int not null auto increment primary key    col1 int    col2 varchar 10      then  to copy values from one row to the other row with new key value  following query may help    insert into my table  col1  col2   select col1  col2 from my table where pk id      This will generate a new value for pk id field and copy values from col1  and col2 of the selected row   You can extend this sample to apply for more fields in the table   UPDATE  In due respect to the comments from JohnP and Martin -    We can use temporary table to buffer first from main table and use it to copy to main table again  Mere update of pk reference field in temp table will not help as it might already be present in the main table  Instead we can drop the pk field from the temp table and copy all other to the main table   With reference to the answer by Tim Ruehsen in the referred posting     CREATE TEMPORARY TABLE tmp SELECT   from my table WHERE      ALTER TABLE tmp drop pk id    drop autoincrement field   UPDATE tmp SET        just needed to change other unique keys INSERT INTO my table SELECT 0 tmp   FROM tmp  DROP TEMPORARY TABLE tmp    Hope this helps

User · Answer

THIS WORKS FOR DUPLICATING ONE ROW ONLY   Select your ONE row from your table Fetch all associative unset the ID row  Unique Index key  Implode the array 0  keys into the column names  Implode the array 0  values into the column values Run the query   The code     qrystr    SELECT   FROM mytablename  WHERE id       rowid    qryresult    this- gt connection- gt query  qrystr     result    qryresult- gt fetchAll PDO  FETCH ASSOC    unset  result 0   id       Remove ID from array   qrystr     INSERT INTO mytablename     qrystr           implode      array keys  result 0            qrystr      VALUES     implode        array values  result 0             result    this- gt connection- gt query  qrystr    return  result    Of course you should use PDO bindparam and check your variables against attack  etc   but gives the example  additional info  If you have a problem with handling NULL values  you can use following codes so that imploding names and values only for whose value is not NULL    foreach   result 0  as  index   gt   value        if   value     null  unset  result 0   index

[mysql] Copy row but with new id

Examples related to mysql