6

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...

  • It's a bit difficult to follow your logic. Maybe if you gave us something a bit more concrete - can you give us something like a table definition(s)(SHOW CREATE TABLE My_Table\G - if they exist already). Or, expand on your question... I'm just not getting it. – Vérace Jun 27 '16 at 22:28
  • Sorry... thanks... I hope the above brings clarity... – fiprojects Jun 28 '16 at 7:26
  • Don't say CHAR and utf8 -- your hashes are taking 120 bytes unnecessarily. – Rick James Jul 9 '16 at 0:22
  • @RickJames I don't know what you mean however the output came from MySQL Workbench tool when I did a SHOW CREATE TABLE. In reality, I have set the charset when doing create database, and I do not charset during create table. – fiprojects Jul 9 '16 at 18:27
5

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' ;
  • Lovely Juvely... I raise my hat and bow my head in appreciation... While I was aware that insert into/select from existed I was less familiar on how I could apply it. I like this solution because I can understand it (and therefore support it within my code). Thanks lots! – fiprojects Jun 28 '16 at 11:39
  • I love the simplicity, thanks for the edit/clarification. It saves me using PHP to select, change and insert. – fiprojects Jun 28 '16 at 11:46

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.