Update a table using info from a second table and a condition from a third table in Postgresql

Question

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?

Answer 1

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 the WHERE clause, and remove the updated table itself product_template from the FROM clause.

UPDATE product_template
SET 
list_price = product_uom.factor * product_template.list_price
FROM product_uom, product_product
WHERE product_product.manufacturer = 2646 and
   product_uom.id = product_template.uom_id and
   product_product.id = product_template.id  

(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.)

Answer 2

Read this link: http://www.postgresql.org/docs/9.1/static/sql-update.html

from_list
A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement.
Note that the target table must not appear in the from_list, unless you intend a self-join
(in which case it must appear with an alias in the from_list).

You need to use an alias to assign different name to second product_template in your query, and replace product_template with this alias in places where you want to refer to this second table.
PostgreSql doesn't know which one product_template you mean in below places in the query (the first, or the second one). I don't know too.

UPDATE product_template
SET ....... * product_template.list_price
...........
INNER JOIN product_template ON ...... = product_template.uom_id
INNER JOIN  ..........  ON ...... = product_template.id