sql - Update values in a column based on a comparison of two other tables -

in postgres have 3 tables joined 2 keys:

key joins tables 1 , 2, not unique in table 1, unique in table 2 , not exist in table 3

key b joins tables 2 , 3 not exist in table 1 , unique in tables 2 , 3.

---

i looking update column in table 3 should hold count of records in table 1 (all values in column 0).

table_1

+-----+ | key | +-----+ | a1  | | a1  | | a1  | | a2  | | a2  | | a3  | | a3  | +-----+ 

table_2

+-------+-------+ | key_a | key_b | +-------+-------+ | a1    | b1    | | a2    | b2    | | a3    | b3    | +-------+-------+ 

table_3 (desired result)

+-------+--------+ | key_b | count  | +-------+--------+ | b1    |      3 | | b2    |      2 | | b3    |      2 | +-------+--------+ 

---

i'm stuck @ update command (not familiar enough them yet), i've got counts need with:

select table_3.key_b, count(table_1.*) table_1 join table_2 on table_1.key_a = table_2.key_a join table_3 on table_2.key_b = table 3.key_b group 1 

i'm not quite sure how update the records in table 3 right counts. think might need function i'm not sure. on right track?

create or replace function my_funct   returns varchar $body$ declare       r         record begin       select key_b table_3 r;        update table_3       set count = (                     select count(*)                     table_1                    join table_2 on table_1.key_a = table_2.key_a                    join table_3 on table_2.key_b = table 3.key_b                    key_b = r                    );   end $body$ 

avoid expensive correlated subquery:

update t3 set c = t.c (     select t2.b, count(*) c     t1 join t2 on t1.a = t2.a     group 1 ) t t3.b = t.b ; table t3;  b | c  ---+---  1 | 3  2 | 2  3 | 2 

as understand key_b in table_2 foreign key table_3 not necessary join table_3 in from clause.

create table t2 (a int primary key, b int unique); create table t1 (a int references t2); create table t3 (b int unique references t2, c int);  insert t2 (a, b) values (1,1),(2,2),(3,3); insert t1 (a) values (1),(1),(1),(2),(2),(3),(3); insert t3 (b) values (1),(2),(3);