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?
