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.
This question is related to
mysql
SET @table = 'the_table';
SELECT GROUP_CONCAT(IF(COLUMN_NAME IN ('id'), 0, CONCAT("\`", COLUMN_NAME, "\`"))) 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;
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]);
}
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.
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.
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';
Source: Stackoverflow.com