mysql - Update one table only after checking the values of another table -

i wonder if me out problem. i'd update column "result" in table1 set "1" when id(table1) belonging tab1id-values have in column result value 1. id in table1 in relationship tab1id in table2. hope make clear:

table1:

id | result | 1  |  null  |        2  |  null  |       3  |  null  |    

table2:

id | tab1id | result | 1  |    1   |   1     2  |    1   |   0    3  |    1   |   1     4  |    2   |   1    5  |    2   |   1     6  |    2   |   1       7  |    3   |   0    8  |    3   |   1    9  |    3   |   1 

the update-satement should update table1 this:

table1:

id | result | 1  |    0   |        2  |    1   |       3  |    0   | 

     result @ id2 1 now, because belonging tab1id (2) rows in table2 have result-value 1

how can 1 update-statement?

this query assumes values table2.result 1 , 0.

update  table1         inner join         (             select  tab1id, count(*) = sum(result) result                table2             group   tab1id         ) b on a.id = b.tab1id set     a.result = b.result 

• sqlfiddle demo