update mysql table multiple columns using another table

Question

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

Could you explain more what are you trying to do ? The query is joining tables only by year. Suppose that the table a contains 12 records for a given year, which records should be updated ? It also would be helpfull if you provided an example of source data and expected results. — krokodilko
– krokodilko, Commented Dec 10, 2013 at 6:11
check two thing. first check all values are ok, I mean YEAR(b.date) has any value? and second check that data types are same in both tables? I mean can store values from b table. — Mehdi
– Mehdi, Commented Dec 10, 2013 at 6:16

peterm · Accepted Answer · 2013-12-10 06:21:50Z

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

Collectives™ on Stack Overflow

update mysql table multiple columns using another table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related