How to duplicate row sql?

790 Asked by alexDuncan in SQL Server , Asked on Oct 4, 2022

The closest I can find to what I want to do is How to duplicate related rows, 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 it's almost a duplicate of the original data).

Table menuship is a hash table for a food menu (each row in the table will identify a menu category (starter, main course, or dessert for example), and products (fish and chips).

Table structure:

CREATE TABLE `menuship3` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `headache` chart(40) DEFAULT NULL,
  `menu card hash` char(40) DEFAULT NULL,
  `menucathash` char(40) DEFAULT NULL,
  `product hash` char(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `headache` (`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 menu card hash menucathash product hash
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 menucardhash aaa (rows 1-5), so I will end up with a table containing 12 records. The extra records will have new menu carwash qqq instead of menu card hash aaa.
id headhash menu card hash menucathash product hash
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?

Answered by Ajit yadav

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 row sql from and 'qqq' with the new value:

INSERT INTO menuship3

    (headhash, menucardhash, menucathash, producthash)

SELECT 

    headhash, 'qqq', menucathash, producthash

FROM 

    menuship3

WHERE 

    menucardhash = 'aaa' ;

How to duplicate row sql?

Your Answer