Search code examples
sqlpostgresqlcountsql-updatesubquery

postgres update value based on count of associated join table

I have a simple scenario TableA, TableB and JoinTable that joins TableA and TableB. I want to store in TableA for each row in TableA the count of records in JoinTable that have TableAId. I can select it correctly as follows:

SELECT "Id", (SELECT COUNT(*) FROM "JoinTable" WHERE "JoinTable"."TableAId" = "TableA"."Id") 
AS TOT FROM "TableA" LIMIT 100

However I'm having a hard time writing an update query. I want to update TableA.JoinCount with this result.

Solution

  • You can use a correlated subquery:

    update tablea a
set tot = (
    select count(*)
    from jointable j
    where t.tableaid = a.id
)

    This updates all rows of tablea with the count of matches from jointable; if there are not matches, tot is set to 0.

    I would not necessarily, however, recommend storing such derived information. While it can easily be intialized with the above statement, maintaining it is tedious. You will soon find yourself creating triggers for every DML operation on the join table (update, delete, insert). Instead, you could put the information in a view:

    create view viewa as
select id, 
    (select count(*) from jointable j where j.tableaid = a.id) as tot
from tablea a

    Side note: in general, don't use quoted identifiers in Postgres. This this link for more.