Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need an Update Query Using several tables 1

Status
Not open for further replies.

elentz

Technical User
Jan 9, 2007
81
US
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:

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



 
Code:
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
 
thanks!

But when I run the query I get:

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


 
oops, sorry, i made a goof

try this --
Code:
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
 
Thanks That works beautifully!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top