Search code examples
postgresqljoincountupdates

Updating a column by counting data from another table

I have two tables let’s say A & B and would like to count the results of column2 in table B by comparing them to table A column2 and update them in table A column1.

I am using the script shown here, but it's taking a really long time so I'd appreciate it if somebody could provide an alternative / better and faster option/script

UPDATE tableA
SET tableA.column1 = (SELECT COUNT(*)
                      FROM tableB 
                      WHERE tableA.column2 = tableB.column2)
Solution

  • Use the proprietary UPDATE ... FROM to perform a join that can be something else than a nested loop:

    UPDATE tableA SET tableA.column1 = tbc.count
FROM (SELECT column2,
             count(*) AS count
      FROM tableB
      GROUP BY column2) AS tbc
WHERE tableA.column2 = tbc.column2;