Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 3 down vote accepted

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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