php - MySQL Get AVR and save in New field -

hello have problem , can't find solution: have 4 tables same structure example follows:

1. table 1: result
2. table 2: store 1
3. table 3: store 2

4. table 4: store 3

   • tables fields: id - code - name - value

i need query read "value" each specific record tables (store 1 - store 2 - store 3) , calculate average , save in table (result)... , go on next record until it's done.

note: i'm using php , mysql...

thanks in advanced...

select     result.id,     result.`code`,     result.`name`,     result.value,     term1.value,     term2.value,     term3.value result inner join store1 on result.`code` = store1.`code` inner join store2 on result.`code` = store2.`code` inner join store3 on result.`code` = store3.`code` result.`code` = 123456 order result.serial asc 

the average sum of values divided number of values (3), grade school arithmetic.

update result r join store1 s1 on s1.code = r.code join store2 s2 on s2.code = r.code join store3 s3 on s3.code = r.code set r.value = (s1.value+s2.value+s3.value)/3 

to lots of columns, can generate sql in php:

$cols = array('col1', 'col2', 'col3', ...); $sets = implode(', ', array_map(function($col) {     return "r.$col = (s1.$col + s2.$col + s3.$col)/3"; }, $cols)); $sql = "update result r         join store1 s1 on s1.code = r.code         join store2 s2 on s2.code = r.code         join store3 s3 on s3.code = r.code         set $sets"; 

if you're using php before 5.3.0, can define named function call array_map

function make_assignment($col) {     return "r.$col = (s1.$col + s2.$col + s3.$col)/3"; } $sets = implode(', ', array_map('make_assignment', $cols));