Help with using one table to insert into another

[elentz]

Can someone give me an example?  I have an update query that uses two manual inputs from a PHP page.  I put in the recordid and the multiplier and it changes pricing for all the products by the second amount.  Ths issue is that I have 13 records (and it could grow) and it is getting tiresome to manually put in the info.  Here's my query:

 

$query="INSERT INTO
         vtiger_pricebookproductrel
     ( pricebookid
     , productid
     , listprice )
select (".$fieldvalue1.")
     , productid
     , (unit_price *.769) * (".$fieldvalue2.")
  from vtiger_products";

$fieldvalue1 and $fieldvalue2 would be the variables that I need to use from a different table.  The table only has two fields, recordid and markup.

 

Ideally I would like to setup a PHP page to do this with a button and some sort of status , but that is icing on the cake, for now getting the query to work would be great.  Any advice is appreciated

[fenway]

You can easily join the tables or use subqueries to pull those values.

[elentz]

Thanks I hadn't thought of joining the tables in a query  Here's what I have so far:

 

SELECT
vtiger_pricebookcf.cf_704,
vtiger_pricebook.bookname,
vtiger_pricebook.pricebookid,
vtiger_pricebook.description,
vtiger_pricebookcf.pricebookid,
vtiger_pricebook.active,
vtiger_pricebookproductrel.productid,
vtiger_pricebookproductrel.listprice,
vtiger_productcf.cf_450
FROM
vtiger_pricebook
Inner Join vtiger_pricebookcf ON vtiger_pricebook.pricebookid = vtiger_pricebookcf.pricebookid
Inner Join vtiger_pricebookproductrel ON vtiger_pricebookcf.pricebookid = vtiger_pricebookproductrel.pricebookid
Inner Join vtiger_productcf ON vtiger_pricebookproductrel.productid = vtiger_productcf.productid

 

 

This gets me a list or output of all the products info.  Now I need to insert into vtiger_pricebookproductrel.listprice the result of vtiger_productcf.cf_450 * vtiger_pricebookproductrel.listprice into table vtiger_pricebookproductrel  In the end I need new info into the vtiger_pricebookproductrel table including the pricebookid, and the listprice.  How difficult is that?

 

Thanks

[fenway]

You can use:

 

INSERT INTO yourTable( col1, col2, col3 ) SELECT c3, 'foo', c4, 'bar' FROM ..... WHERE ....

 

As you had done earlier....