Copy row but with new id

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.


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->connection->query($qrystr);
 $result = $qryresult->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->connection->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 => $value) {
    if ($value === null) unset($result[0][$index]);
}

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.

Tags:

Mysql

Recent Posts