Insert data from column as average in another table in mysql-CodePudding

I have a table named feedback which has columns mechEmail (foreign key) and rate in it.

I have another table named mechanical which has columns email (primary key) and avgrate.

I want to insert the average rate from the feedback table into the mechanical table where mechEmail matches email.

Here's the code that I used, but it only selects - but does not insert:

SELECT 
    mechanical.email, 
    COALESCE(AVG(feedback.rate), 0) AS rate
FROM 
    mechanical
LEFT JOIN 
    feedback ON feedback.mechEmail = mechanical.email
GROUP BY 
    mechanical.email;

CodePudding user response：

It seems you don't want an Insert statement but an UPDATE statement as you already have email in mechanical table -

UPDATE mechanical M
  JOIN (SELECT mechEmail, AVG(rate) rate
          FROM feedback
         GROUP BY mechEmail) F ON F.mechEmail = M.email
   SET M.avgrate = F.rate;