PostgreSQL: update tabA by choosing from tabB and tabC

Question

I have a choice in tabC. I applied this selection to tabB. Now I have to update tabA with the values ​​from these two options.

SELECT on tabC and tabB:

SELECT * FROM tabC
WHERE id_field IN
  (SELECT id_field FROM tabB WHERE date_IN = '2011-02-22')
ORDER BY id_field

UPDATE tabA:

UPDATE tabA
SET field_1 = tabC.field_1, field_2 = tabC.field_2, field_2 = tabC.field_2
FROM tabC WHERE tabA.id_field IN
  (SELECT tabC.id_field FROM tabC WHERE tabC.id_field IN
    (SELECT id_field FROM tabB WHERE date_IN = '2011-02-22'))

The UPDATE statement is executed without any errors, but the result is not what I used: fields 3 have the same values ​​for all rows. What's wrong?

Answer 1

Use an inner join instead

UPDATE tabA
   SET field_1 = tabC.field_1,
       field_2 = tabC.field_2,
       field_3 = tabC.field_3 
  FROM tabC
       INNER JOIN tabB ON tabC.id_field = tabB.id_field AND tabB.date_IN = '2011-02-22'
 WHERE tabA.id_field = tabC.id_field;