PostgreSQL – How to Update Column with Data from Another Table

join;postgresqlupdate

I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and I want to update the third:

C [ID, column1, column2,column3]

I am updating another third table using this query. 

UPDATE C 
  set column1=t.firstTab, column2=t.secondTab, column3=t.thirdTab 
  from (select A.column1 as firstTab, B.column2 as secTab, 
              (A.column1 + B.column2) thirdTab 
        from A, B limit 1; ) as t ;

I got: 

UPDATE 0

When I run this query: 

select A.column1 as firstTab, B.column2 as secTab, (A.column1 + B.column2) thirdTab 
            from A, B limit 1;

I got results. Am I missing something ?

Sample data: http://sqlfiddle.com/#!15/e4d08/5

Best Answer

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 and B (i.e. without any joining condition) and then choosing an arbitrary row (LIMIT 1 without ORDER BY). It then uses the values from that arbitrary row to update all rows of table C. If you want different values to be used for different rows of C, you'll have to join the 3 tables (using JOIN - ON and WHERE)

Refer to the manual on UPDATE for details.

Related Question