mysql - 如何使用另一个计算平均值的查询的结果更新MySQL表

Question

我想用其他表中所有价格值的平均值更新一个表。似乎无法让它工作。

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);

以上返回受影响的 0 行。

运行这个确实会给我平均价格；

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;

或者，我也很高兴将上述计算的值保存为 PHP 变量，但也无法使其发挥作用。

Answer 1

括号有问题。试试这个:

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
                );