CODEWITHSUNDEEP
mysqlsql
user1222302
user1222302

Reputation: 31

How to update MySQL table with result from another query which calculates average

I'm wanting to update a table with the AVG of all price values in my other tables. Can't seem to get it to work.

Update price_avg set price = (select avg(price) from ((select price from table1) union all
  (select price from table2) union all
  (select price from table3) union all
  (select price from table4) union all
  (select price from table5) union all
  (select price from table6) union all
  (select price from table7) )) as t);

The above returns 0 rows affected.

Running this does give me the average price;

select avg(price) from ((select price from table1) union all
  (select price from table2) union all
  (select price from table3) union all
  (select price from table4) union all
  (select price from table5) union all
  (select price from table6) union all
  (select price from table7)) as t;

Alternatively I'm also happy to have the value of the above calculation saved as a PHP variable, but also couldn't get that to work.

Upvotes: 0

Views: 99

Answers (2)

user1222302
user1222302

Reputation: 31

It looks like what I really wanted to do was use insert instead of update. That's all that needed to be changed. My table was empty so there was nothing to update. Using update I should have specified the row to be updated using a WHERE command with it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Something is amiss with the parentheses. Try this:

Update price_avg
    set price = (select avg(price)
                 from ((select price from table1) union all
                       (select price from table2) union all
                       (select price from table3) union all
                       (select price from table4) union all
                       (select price from table5) union all
                       (select price from table6) union all
                       (select price from table7)
                      ) t
                );

Upvotes: 1

Related Questions