MySQL – Easiest Way to Duplicate Rows

MySQL

The closest I can find to what I want to do is here, but my inexperience gets me lost. Basically, I want to copy a number of records, change one column and insert them back into the same table (so its almost a duplicate of the original data).

More info: Table menuship is a hash table for a food menu (thus each row in the table will identify a menu category (starter, maincourse or desert for example), and products (fish and chips).

Table structure:

menuship3, CREATE TABLE `menuship3` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `headhash` char(40) DEFAULT NULL,
  `menucardhash` char(40) DEFAULT NULL,
  `menucathash` char(40) DEFAULT NULL,
  `producthash` char(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `headhash` (`headhash`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8

In programming circles, I would say I want to "fork" my data…

If my table content was like so:

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 1,  aaa    ,   aaa       ,   aaa      ,   aaa
 2,  aaa    ,   aaa       ,   aaa      ,   bbb
 3,  aaa    ,   aaa       ,   aaa      ,   ccc
 4,  aaa    ,   aaa       ,   bbb      ,   ddd
 5,  aaa    ,   aaa       ,   ccc      ,   eee
 6,  aaa    ,   other     ,   xyz      ,   fgi
 7,  aaa    ,   other     ,   xyz      ,   fgh

I want to duplicate all records with menucardhashaaa (rows 1-5), so I will end up with a table containing 12 records. The extra records will have new menucarhashqqq instead of menucardhashaaa.

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 8,  aaa    ,   qqq       ,   aaa      ,   aaa
 9,  aaa    ,   qqq       ,   aaa      ,   bbb
10,  aaa    ,   qqq       ,   aaa      ,   ccc
11,  aaa    ,   qqq       ,   bbb      ,   ddd
12,  aaa    ,   qqq       ,   ccc      ,   eee

The result in effect means I have records that have similarity, records 1-5 are similar to records 8-12 – the differences being the id and menucardhash columns.

I was just going to select the records within PHP, change menucathash and send them back to the db, but I wondered if there was an SQL query that could do this for me and thus reduce cpu cycle overhead.

Is this possible via one or two queries, or am I better off having PHP carry the weight?

Thanks in advance…

Best Answer

This is a very simple INSERT .. SELECT query.

The id is not included in the column list as it has the AUTO_INCREMENT property and will get values automatically. You only need to replace 'aaa' with the menucardhash value that you want to duplicate from and 'qqq' with the new value:

INSERT INTO menuship3
    (headhash, menucardhash, menucathash, producthash)
SELECT 
    headhash, 'qqq', menucathash, producthash
FROM 
    menuship3
WHERE 
    menucardhash = 'aaa' ;
Related Question