Re: Syntax for UPDATING a table with result of an Aggregate (GROUP BY) Inner Join Function

[Peter Brawley]

> Is is possible to directly UPDATE columns in a table with the result of an Aggregate query?

Yes, eg given the toy table(yr,val,max) you could write ...

update t
join (
  select x.yr,max(y.val) as max
  from t x
  join t y on x.yr between y.yr and y.yr+5
  group by x.yr
) s using(yr)
set t.max=s.max;

... but as soon as a `yr` or `val` value changes, the max column goes incorrect;o it's bad practice to include row-to-row total values in a table, better to keep such totals in another table, or in a view.