In postgres I have 3 tables that are joined by two keys:
Key A joins tables 1 and 2, is not unique in table 1, is unique in table 2 and does not exist in table 3
Key B joins tables 2 and 3 does not exist in table 1 and is unique in tables 2 and 3.
I am looking to update a column in table 3 that should hold a count of records in table 1 (all values in that column are 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 at the update command (not familiar enough with them yet), I've got the counts I need with:
Select Table_3.Key_B, count(Table_1.*)
from 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 by 1
I'm just not quite sure how to update the the records in table 3 with the right counts. I think I might need a function but I'm not sure. Is this on the right track?
Create or replace function my_funct
returns varchar as
$body$
Declare
r record
begin
select key_B from Table_3 into r;
update Table_3
set count = (
select count(*)
from 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
Where key_B = r
);
end
$body$
Avoid an expensive correlated subquery:
update t3
set c = t.c
from (
select t2.b, count(*) as c
from t1 join t2 on t1.a = t2.a
group by 1
) t
where t3.b = t.b
;
table t3;
b | c
---+---
1 | 3
2 | 2
3 | 2
As I understand key_B in table_2 is a foreign key to table_3 so it is not necessary to join table_3 in the 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 into t2 (a, b) values (1,1),(2,2),(3,3);
insert into t1 (a) values (1),(1),(1),(2),(2),(3),(3);
insert into t3 (b) values (1),(2),(3);