CODEWITHSUNDEEP
mysql
user1967034
user1967034

Reputation: 59

Update multiple rows with averages using mysql

I'm getting pretty close to answering this using other answers but am just struggling to get over the line.

I have a client table structure that cannot be altered it consists of many tables but the two I'm interested in are as follows (simplified):

Table1: ID1 (PK), ID2 (PK), ID3 (PK), AVGPercent (currently null)

Example:

ID1, ID2, ID3, AVGPercent
a, b, c, NULL
e, f, g, NULL

Table2: ID1 (PK), ID2 (PK), ID3 (PK), ID4 (PK), Percent

Example:

ID1, ID2, ID3, ID4, Percent
a, b, c, d, 88
a, b, c, e, 80
e, f, g, d, 92

I can get a list of distinct things in Table2 using the following SQL:

SELECT ID1, ID2, ID3, avg(Percent) FROM Table2 group by ID1, ID2, ID3

What I need to do is update Table1 to fill AVGPercent so that it looks like this:

ID1, ID2, ID3, AVGPercent
a, b, c, 84
e, f, g, 92

I can do it for one row easily and I can use php to select from AVG(Percent) from Table2 and then loop through the 26,000 records updating AVGPercent in Table1 but is there a way to update all of AVGPercent in Table1 all in one go?

Upvotes: 0

Views: 47

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can use UPDATE using JOINS like below

UPDATE Table1 AS t1
INNER JOIN
(
   SELECT ID1, ID2, ID3, 
       avg(Percent) as AvgPercent
   FROM Table2 
   GROUP BY ID1, ID2, ID3
) As t2
    ON t1.ID1=t2.ID1 AND t1.ID2=t2.ID2 AND t1.ID3=t2.ID3
SET t1.AVGPercent  = t2.AvgPercent

Upvotes: 1

Related Questions