Kilisi Kilisi - 1 month ago 12
SQL Question

Update multiple columns in a table from another tables data including nulls

I have two tables with 6 columns, in one table (the one I want to update) the first three columns are already populated, the other three I just made so they're empty.

Summary table

Number, ID, height, weight, volume, density
1 1 5
2 2 5
3 3 12


I have another table fully populated where the same ID is used but the other data fluctuates and it doesn't have the height

Daily table

Number, ID, name, weight, volume, density
1 1 c3 23 10
2 2 c17 24.2 1 5
3 3 c12 22 2 6
4 1 c3 21 2
5 2 c17 25 8


I want to take the last weight, volume, density values from the Daily table and use them to populate those columns in the Summary table. Both tables have thousands of entries with the daily one close to a million.

Result should be Summary table changes to

Number, ID, height, weight, volume, density
1 1 5 21 2
2 2 5 25 8
3 3 12 22 2 6


I can do it bit by bit using info I got from this question, but I want to do it all at once.
Please assist.

Answer

Something like this:

update summary 
  set weight = t.weight,
      volume = t.volume, 
      density = t.density
from (
  select distinct on (id) id, weight, volume, density
  from daily
  order by id, number desc
) t
where t.id = summary.id;

The inner select will only return the rows from the daily table with the highest "number" for each id. For other ways of solving that see

Online example: http://rextester.com/AWT29305