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

update addresses set cid=id where id in (select id from customers)

Answer 3

Officially, the SQL languages does not support a JOIN or FROM clause in an UPDATE statement unless it is in a subquery. Thus, the Hoyle ANSI approach would be something like

Update addresses
Set cid = (
            Select c.id
            From customers As c
            where c.id = a.id
            )
Where Exists    (
                Select 1
                From customers As C1
                Where C1.id = addresses.id
                )

However many DBMSs such Postgres support the use of a FROM clause in an UPDATE statement. In many cases, you are required to include the updating table and alias it in the FROM clause however I'm not sure about Postgres:

Update addresses
Set cid = c.id
From addresses As a
    Join customers As c
        On c.id = a.id