PostgreSQL - copy column from related table

Question

So I have three tables: companies, addresses and company_address.

For optimization reasons I need to copy city column from addresses table to companies table. Relation between companies and addresses is many to one (as many companies can occupy same address). They are connected through company_address table, consisting of address_id and company_id columns.

I found this solution for case without intermediate table: How to copy one column of a table into another table's column in PostgreSQL comparing same ID

Trying to modify query I came up with:

UPDATE company SET company.city=foo.city
FROM (
    SELECT company_address.company_id, company_address.address_id, address.city 
    FROM address LEFT JOIN company_address 
    ON address.id=company_address.address_id
) foo
WHERE company.id=foo.company_id;

but it gives error:

ERROR: column "company" of relation "company" does not exist

I cant figure out what is going on. I'll be grateful for any ideas.

Answer 1

You don't need a subquery for that. Also, refer in the SET clause to your table columns without preceding with table name.

I believe that since your WHERE condition includes joined table, it should be INNER JOIN instead of a LEFT JOIN.

UPDATE company c
SET city = a.city
FROM address a
INNER JOIN company_address ca ON a.id = ca.address_id
WHERE c.id = ca.company_id

Note how using aliases for table names shortens the code and makes it readable at the very first glance.

Answer 2

You're right syntactically, you just don't need the table name at the beginning of the update statement:

UPDATE company SET city=foo.city
FROM (
SELECT company_address.company_id, company_address.address_id, address.city 
FROM address LEFT JOIN company_address 
ON address.id=company_address.address_id
) foo
WHERE company.id=foo.company_id;