About this question
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?
