copy row in same table and update just one column in MySQL

Question

I need to copy a row based on some condition(where clause), from a table,make a duplicate entry in same table and update just one column(a foreign_key). But because "select * from Table_name" returns all fields including primary_key it is not able to insert new row error : duplicate key.

I don't know all the columns of table, so i cant ignore primary_key by giving only required column names in select query.

Is there any way to ignore primary_key from fetching itself.. or can i fetch the whole row, and set the primary_key value to null, and then insert the row in table, so that it auto increments the primary_key for new row being added.

Thanks.

Mike Mike · Accepted Answer · 2010-12-08T09:23:47

It is possible to do what you want by querying the information schema tables, and using dynamic SQL to build an appropriate statement. However, you should take great care with this approach, as modifying data in tables for which you do not know the column names could result all sorts of problems:

DROP TABLE mytable;

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  `col3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO mytable (col1, col2, col3) VALUES ('A', 'B', 'C');

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
+----+------+------+------+

SET @columns := (
  SELECT
    GROUP_CONCAT(column_name)
    FROM information_schema.columns
    WHERE table_schema = 'test'
    AND table_name = 'mytable'
    AND column_key <> 'PRI'
);

SET @sql := (
  SELECT CONCAT(
    'INSERT INTO mytable (', @columns, ') ',
    'SELECT ', @columns, ' FROM mytable ',
    'WHERE id = 1;'
  )
);

SELECT @sql;

+---------------------------------------------------------------------------------------+
| @sql                                                                                  |
+---------------------------------------------------------------------------------------+
| INSERT INTO mytable (col1,col2,col3) SELECT col1,col2,col3 FROM mytable WHERE id = 1; |
+---------------------------------------------------------------------------------------+

PREPARE stmt FROM @sql;

EXECUTE stmt;

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
|  2 | A    | B    | C    |
+----+------+------+------+

copy row in same table and update just one column in MySQL

3 Answers