update query with join on two tables

Question

I have customer and address tables.

Query:

SELECT * FROM addresses a,      customers b WHERE a.id = b.id 

returns 474 records

For these records, I'd like to add the id of customer table into cid of address table.

Example: If for the first record the id of customer is 9 and id of address is also 9 then i'd like to insert 9 into cid column of address table.

I tried:

UPDATE addresses a,        customers b SET a.cid = b.id WHERE a.id = b.id 

but this does not seem to work.

Answer 1

this is Postgres UPDATE JOIN format:

UPDATE address  SET cid = customers.id FROM customers  WHERE customers.id = address.id 

Here's the other variations: http://mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html

Answer 2

Using table aliases in the join condition:

update addresses a set cid = b.id  from customers b  where a.id = b.id