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 
        , configid
        , optionname
        , sortorder
        , hidden
    , 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?

  • Are there existing rows with configid=76 or the just inserted are the only ones? Commented Sep 12, 2012 at 22:39
  • Just inserted ones to come
    – jfreak53
    Commented Sep 12, 2012 at 22:43
  • Does the tblproductconfigoptionssub table have any other unique key (besides the id)? Commented Sep 12, 2012 at 22:46
  • 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. Commented Sep 12, 2012 at 23:27

1 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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.