Postgresql Update with subquery

Question

UPDATE Customer C
SET name = B.name, 
age = B.age
FROM (SELECT A.*, B.* FROM CUSTOMER_TEMP WHERE A.ID = B.ID) AS B

I got a sql as above, after I run the query, it update all my rows to the same result.

I wonder I need a where after

UPDATE Customer C
SET name = B.name, 
age = B.age
FROM (SELECT A.*, B.* FROM CUSTOMER_TEMP WHERE A.ID = B.ID) AS D
WHERE C.ID = D.ID

But i got this id is ambiguous for the last query.

Answer 1

Try this

update Customer set
    name = B.name, 
    age = B.age
from Customer as C
    inner join CUSTOMER_TEMP as B on B.ID = C.ID

SQL FIDDLE EXAMPLE