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?

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 ;