MySQL Insert into two tables using new IDs

Question

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.

table1 contains configuration options, table2 contains pricing data. Table2 uses the auto ID from table1.

I have all these products in Table1 and I have to use the same config options in another group in that table, but WHMCS doesn't have copy for config options.

I was thinking of going into the table and running a select query like so:

INSERT INTO tblproductconfigoptionssub 
 (
 id
 , configid
 , optionname
 , sortorder
 , hidden
 ) 
SELECT 
 NULL
 , 76
 , optionname
 , sortorder
 , hidden 
FROM tblproductconfigoptionssub 
WHERE configid = 41

That works for data from the first table, by selecting all records from config group id 41 and reinsert them, except now they are linked to config group 76.

Table2 contains the pricing data and it has a column called relid that is linked to the ID generated from tblproductconfigoptionssub above.

Table2 already has pricing for the items in table1 under their original IDs. The records from Table2 would have to be copied, but with the new IDs generated by the query above.

How do I modify the statement above to insert the new data into Table2, along with the prices?

I don't really want to do a PHP script for this. However, I think I need to make a MySQL function to select all items from config group 41 and then store values in variables. Then insert into two tables.

Is there a way to do this with INSERT statements?

• ypercubeᵀᴹ: Are there existing rows with configid=76 or the just inserted are the only ones?
• jfreak53: Just inserted ones to come
• ypercubeᵀᴹ: Does the tblproductconfigoptionssub table have any other unique key (besides the id)?
• ypercubeᵀᴹ: As a note, it's usually better if you include the tables' definitions (CREATE TABLE...) and possibly some sample rows. For many people it's much easier to read code than a long explanation.

Answer 1

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
 ( relid, price, ... ) --- relid and all the other columns, 
 --- except any autoincrement you may have
SELECT pasted.id, p.price, .... --- and the same columns here
FROM 
 pricing AS p
 JOIN
 tblproductconfigoptionssub AS copied
 ON copied.id = p.relid
 AND copied.configid = 41 
 JOIN
 tblproductconfigoptionssub AS pasted
 ON pasted.optioname = copied.optioname
 AND pasted.configid = 76 ;