19

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 its 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,
  `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 menucardhash aaa (rows 1-5), so I will end up with a table containing 12 records. The extra records will have new menucarhash qqq instead of menucardhash aaa.

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?

0
28

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' ;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.