CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5107

Updating column in table based on multiple select values

Running a manual example in my IDE, I'm selecting an average based on a count and the sum of one of my columns like so:

SELECT
    sum(velocity)/count(*) as avg
from metrics
   where repnumber = 397
   and material = 1051
   and item = 1150
   and color = 99;

But I want to update the column repvelocity based on the above select.

So for every row, I want it to select the columns like above, and set repvelocity to the average

So I want:

material  |  item  |  color  |  velocity  |  repnumber  |  repvelocity
------------------------------------------------------------------------
1               2       2           5           123          4.2
1               2       2           2           123          4.2
1               2       2           5           123          4.2
1               2       2           4           123          4.2
1               2       2           5           123          4.2

2               3       7           7           123          3.8
2               3       7           2           123          3.8
2               3       7           3           123          3.8
2               3       7           2           123          3.8
2               3       7           5           123          3.8

Basically, I have duplicate rows because the 'velocity' column is attributed to customers. So every where there is a combo of the repnumber, material, cover, color I want to update the repvelocity to the average from above.

You can see it looked for every row that has material 1, item 2, color 2 and repnumber 123, did the count/sum average, and then updates the repvelocity for each row.

Is there a way I can do a whole-table update like this?

Upvotes: 1

Views: 246

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use a join:

update metrics m join
       (select repnumber, material, item, color, sum(m2.velocity)/count(*) as avg_velocity
        from metrics m2
        group by repnumber, material, item, color
       ) m2
       using (repnumber, material, item, color)
    set m.repvolocity = m2.avg_velocity;

I also wonder why you are using sum(velocity)/count(*). I would expect avg(velocity). The two are not exactly the same, but only differ if velocity is ever NULL.

In most other database, you would use a correlated subquery:

update metrics m
    set repvolocity = (select avg(m2.velocity)
                       from metrics m2
                       where m2.repnumber = m.repnumber and m2.material = m.material and m2.item = m.item and m2.color = m.color
                      );

Upvotes: 2

Raj
Raj

Reputation: 1

Using the avg() function will be the best choice You can go for something like this if you do not want to use avg().

SELECT a.,aa.repvelocity FROM metrics a JOIN ( SELECT SUM (velocity) / COUNT () repvelocity, repnumber FROM metrics b WHERE b.repnumber = 397 AND b.material = 1051 AND b.item = 1150 AND b.color = 99 GROUP BY b.repnumber) aa ON aa.repnumber = a.repnumber;

I have just used repnumber to group and calculate.

Upvotes: -1

Related Questions