Update column with highest value from another table in Postgres?

November 6, 2021

I have 2 tables, tables A and B. Table A contains a counter (among other columns) that keeps track of the highest version of that item in table B, and table B has information about the item.

-- Table A
+----+-------+
| ID | Total |
+----+-------+
|  1 |    50 | -- should be 39
|  2 |    41 | -- should be 40
|  3 |    21 | -- should be 11
+----+-------+

-- Table B, ItemID references ID in table A.
-- Note: Each item from table A has multiple rows in table B
-- Example: Version 39 means that there are 38 other rows of that item. 
+----+---------+--------+
| ID | Version | ItemID |
+----+---------+--------+
|  1 |      38 |      1 | 
|  2 |      39 |      1 | 
|  3 |      40 |      2 |
|  4 |      11 |      3 |
+----+---------+--------+

I’ve now however found a problem where the counter, total, for many items is not in sync with the highest version. Is there a way to UPDATE total in table A with the highest value of version for each item in table B?

It might be worth mentioning that table B contains approx. 22 million rows, whereas table A has around 3800 rows.

Please let me know if anything is unclear and I’ll do my best to clarify. Thanks in advance!

>Solution :

You can try this :

WITH list AS
(
  SELECT ItemID, max(version) AS max_version
    FROM table_B
   GROUP BY ItemID
)
UPDATE table_A AS t
SET Total = l.max_version
FROM list AS l
WHERE t.ID = l.ItemID