Aggregation

UDA
Enthusiast

Aggregation

UPDATE marc SET m2 = sum(m1) + sum(m2) WHERE m1 = 1;

What is a practical solution to this?
3 REPLIES
Junior Contributor

Re: Aggregation

Hi Marc,

strange update but there it is:
UPDATE marc
FROM
(SELECT SUM(m1) + SUM(m2) AS x FROM marc WHERE m1 = 1) dt
SET m2 = dt.x WHERE m1 = 1;

Dieter
Enthusiast

Re: Aggregation

Thanks.
"Strange" - Yes I thought so too.
So I thought why not test it anyway as I didn;t think it was valid.
I have an idea on paper from someone else(that I don't fully understand yet hence this post) and they documented this approach.
Just to compare notes, Why do you consider it to be strange?
Junior Contributor

Re: Aggregation

Hi Marc,
it's strange, because the update is based on two columns and one of those is also used within the where-condition.
What kind of summable information is hidden within that column?
I simply can't imagine any real usage for that :-)

m1 m2
1 10
1 20
1 30

will result in

m1 m2
1 63
1 63
1 63

(1+1+1) + (10+20+30) -> 63

Dieter