Update column with data from another table
The proper form would be (assuming current pg version 9.3 for lack of information):
UPDATE C
SET column1 = A.column1
, column2 = B.column2
, column3 = A.column1 + B.column2
FROM A
JOIN B ON A.id = B.id -- ??? not specified in question!
WHERE C.id = A.id -- ??? not specified in question!
AND (C.column1, C.column2, C.column3) IS DISTINCT FROM
(A.column1, B.column2, A.column1 + B.column2);
The last WHERE
clause is optional to avoid empty updates that would not change anything (but still write a new row version at full cost).
ypercube already gave a basic explanation in his comment:
You don't get duplication. Your derived table is cross joining
A
andB
(i.e. without any joining condition) and then choosing an arbitrary row (LIMIT 1
withoutORDER BY
). It then uses the values from that arbitrary row to update all rows of tableC
. If you want different values to be used for different rows of C, you'll have to join the 3 tables (usingJOIN - ON
andWHERE
)
Refer to the manual on UPDATE
for details.