Update column with highest value from another table in Postgres?-CodePudding

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!

CodePudding user response：

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