CODEWITHSUNDEEP
mysql
srinath madusanka
srinath madusanka

Reputation: 592

update mysql table multiple columns using another table

i have following tables (Table: avg_month_val1)

year                       
month        
evep                
sunshine_hrs             
rainfall     
max_temp               
min_temp

and

(Table: reg_data3)

id         
date                
time                  
rainfall                
evep                         
max_temp                   
min_temp    
sunshine_hrs

i wan to up date avg_month_val1 table using reg_data3 i follow How do you update multiple fields from another table in mysql?

here is my query

UPDATE `avg_month_val1` a,`reg_data3` b SET
a.`year`=YEAR(b.`date`),
a.`month`=MONTH(b.`date`),
a.`evep`=ROUND(AVG(b.evep),2),
a.`max_temp`=ROUND(AVG(b.max_temp),2), 
a.`min_temp`=ROUND(AVG(b.min_temp),2) ,
a.`rainfall`=ROUND(SUM(b.rainfall),2), 
a.`sunshine_hrs`=ROUND(AVG(b.sunshine_hrs),2)
WHERE a.`year`=YEAR(b.`date`)

but it gives following error

Error Code: 1111
Invalid use of group function

how i accomplish this

Upvotes: 0

Views: 565

Answers (1)

peterm
peterm

Reputation: 92845

Try it this way

UPDATE avg_month_val1 t JOIN 
(
  SELECT YEAR(date) year,
         MONTH(date) month,
         ROUND(AVG(evep), 2) evep,
         ROUND(AVG(max_temp), 2) max_temp,
         ROUND(AVG(min_temp), 2) min_temp,
         ROUND(SUM(rainfall), 2) rainfall,
         ROUND(AVG(sunshine_hrs), 2) sunshine_hrs
    FROM reg_data3
   GROUP BY YEAR(date), MONTH(date)
) s 
    ON t.year = s.year
   AND t.month = s.month
   SET t.evep = s.evep,
       t.max_temp = s.max_temp, 
       t.min_temp = s.min_temp, 
       t.rainfall = s.rainfall, 
       t.sunshine_hrs = s.sunshine_hrs

Using UPDATE implies that you already have in table avg_month_val1 rows with columns year and month populated and only want to update data columns (evep, ...) with aggregated values from reg_data3.

But if it's not the case and you want to INSERT all columns including year and month but be able to update existing rows with the same year and month values you can it using ON DUPLICATE KEY clause

INSERT INTO avg_month_val1 (year, month, evep, max_temp, min_temp, rainfall, sunshine_hrs)
SELECT YEAR(date) year,
       MONTH(date) month,
       ROUND(AVG(evep), 2) evep,
       ROUND(AVG(max_temp), 2) max_temp,
       ROUND(AVG(min_temp), 2) min_temp,
       ROUND(SUM(rainfall), 2) rainfall,
       ROUND(AVG(sunshine_hrs), 2) sunshine_hrs
  FROM reg_data3
 GROUP BY YEAR(date), MONTH(date)
 ON DUPLICATE KEY UPDATE
       evep = VALUES(evep),
       max_temp = VALUES(max_temp), 
       min_temp = VALUES(min_temp), 
       rainfall = VALUES(rainfall), 
       sunshine_hrs = VALUES(sunshine_hrs)

In order for this to work you have to make sure that you have UNIQUE or PRIMARY KEY constraint on (year, month) in avg_month_val1

Upvotes: 4

Related Questions