Need an Update Query Using several tables 1

[elentz]

I want to change a field using two other fields. I have no problem getting the fields I want in a Select query but for some reason I have a mental block on how to do an update, since the information is in several tables.

Here is the select Query that works:

SELECT
vtiger_quotes.quoteid,
vtiger_productcf.cf_567,
vtiger_inventoryproductrel.listprice,
vtiger_quotescf.cf_697
FROM
vtiger_quotes
Inner Join vtiger_inventoryproductrel ON vtiger_quotes.quoteid = vtiger_inventoryproductrel.id
Inner Join vtiger_productcf ON vtiger_inventoryproductrel.productid = vtiger_productcf.productid
Inner Join vtiger_quotescf ON vtiger_quotes.quoteid = vtiger_quotescf.quoteid
WHERE
vtiger_quotes.quoteid =  '4682'

The field I need to update is listprice and it needs to be the product of cf_697 * cf_567

Thanks for any help on this

 

[r937]

UPDATE vtiger_inventoryproductrel 
INNER 
  JOIN vtiger_productcf 
    ON vtiger_productcf.productid = vtiger_inventoryproductrel.productid
INNER 
  JOIN vtiger_quotescf 
    ON vtiger_quotescf.quoteid = vtiger_quotes.quoteid
   SET vtiger_inventoryproductrel.listprice
     = vtiger_productcf.cf_567 
     + vtiger_quotescf.cf_697
 WHERE vtiger_inventoryproductrel.id = '4682'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[elentz]

thanks!

But when I run the query I get:

#1054 - Unknown column 'vtiger_quotes.quoteid' in 'on clause'

 

[r937]

oops, sorry, i made a goof

try this --

UPDATE vtiger_inventoryproductrel 
INNER
  JOIN vtiger_productcf
    ON vtiger_productcf.productid = vtiger_inventoryproductrel.productid
INNER 
  JOIN vtiger_quotescf 
    ON vtiger_quotescf.quoteid = vtiger_inventoryproductrel.id
   SET vtiger_inventoryproductrel.listprice
     = vtiger_productcf.cf_567
     + vtiger_quotescf.cf_697
 WHERE vtiger_inventoryproductrel.id = '4682'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[elentz]

Thanks That works beautifully!