PostgreSQL: update tabA with a selection from tabB and tabC
I've a selection in tabC. I've applied that selection to tabB. Now I've to update tabA with values from these two selections.
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
开发者_StackOverflow中文版
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 runs without any error but the result is not what I'd expeted: the 3 fields have the same values for all the rows. What's wrong?
Use 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;
精彩评论