0

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.

Community
  • 1
  • 1
Kilisi
  • 402
  • 11
  • 33
  • How do you identify the "last" row in the daily table? I don't see any timestamp or something similar –  Oct 26 '16 at 14:26
  • @a_horse_with_no_name they're numbered rows, sorry should be six columns now I see what you mean, I'll edit – Kilisi Oct 26 '16 at 14:27

4 Answers4

3

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

1

You can use a windowing funtion to get the latest entry in your daily table for each ID. I've tested this in SQL Server, but I believe the syntax for postgres is the same in this case. 

With LatestDaily As
(
  select *
  from (
    Select RANK() OVER (Partition By ID ORDER BY Number DESC) as r, *
    From Daily
  ) t
  where t.r = 1
)
Update summary s
   Set Weight = d.Weight, Volume = d.Volume, Density = d.Density
From LatestDaily d 
where s.ID = d.ID;
SteveR
  • 199
  • 7
  • Postgres needs the `;` at the end, not at the start. The "greatest-n-per-group" problem is typically faster in Postgres when using `distinct on()` instead of a window function. And the `update` syntax with a join is different in Postgres. You should **not** repeat the target table in the `from` clause. –  Oct 26 '16 at 14:49
  • Good catch. Thanks! I wonder if sql server has an equivalent to postgres' distinct on(). That's a neat function. – SteveR Oct 26 '16 at 14:50
  • I took the liberty to fix the syntax errors in your statement. –  Oct 26 '16 at 14:54
1

Try this:

with Daily as (
select d.number, d.Id, d.weight, d.volume, d.density 
From  daily d 
join (select id, max(number) from daily group by ID) d2 on d.number = d2.number
)
update Summary
set weight = d2.weight
, volume = d2.volume
, density = d2.density
from Daily d2
where id = d2.id
and
(weight<> d2.weight OR
volume <> d2.volume OR
density <> d2.density)
)

This should ensure that you get the correct newest record per id and only those that need updating.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

UPDATE Summary SET weight = _weight, volume = _volume, density =_density FROM ( SELECT ID _ID,weight _weight, volume _volume , density _density FROM Details WHERE Number IN (SELECT MAX(Number) FROM Details GROUP BY ID) ) A WHERE ID = _ID

Mansoor
  • 4,061
  • 1
  • 17
  • 27