My current code is as follows
UPDATE product_template
SET
listprice = product_uom.factor * product_template.list_price
FROM product_uom
INNER JOIN product_template ON product_uom.id = product_template.uom_id
INNER JOIN product_product ON product_product.id = product_template.id
WHERE product_product.manufacturer = 2646
As I understand line 1 specifies which table I want to update. then I specify that I want to update the column named list_price which is in product_template using 2 numeric columns. I specify the second table which has a column with a numeric value I need in order to update my column from the table that is going to be updated. I specify the inner join of the table that is going to be updated and the table that has the info I need to do so. I join the table that is going to be updated with the table that has a column which I need as a condition for the update to happen. the last line states the condition that must be met in order for the update to happen in that row.
As it is, if I try to run this code in postgresql I get the following error ERROR: table name "product_template" specified more than once
I only use product_template to specify which tabkle will be updated and twice more to create the inner joins, what is the correct way of of updating this table while using information from 2 different tables?
Unfortunately, you can't use the
JOIN...ON
syntax to join to the table that is being updated to the rest of the tables. You have to move those join conditions into theWHERE
clause, and remove the updated table itselfproduct_template
from theFROM
clause.(This is assuming you do not want to do a self join, which it seems you don't. If you do want a self-join, then you need to assign a table alias, just like with non-update self joins.)