MySQL query question -

i have mysql table contains many rows. table structure follows:

id: bigint, event_type: int, total: int 

sample data:

id    event_type    total 1      1            null 2     -1            null 3      1            null 4      1            null 5      1            null 6     -1            null 7     -1            null 8     -1            null 

the event_type either 1 or -1. total set null. there simple sql query accumulate values of event_type in total. so, table like:

id    event_type    total 1      1            1 2     -1            0 3      1            1 4      1            2 5      1            3 6     -1            2 7     -1            1 8     -1            0 

also, total column can partially calculated. in other words, need run query while table still being modified (by insertions). know can done using php or perl code. however, nice using sql queries.

since indeed mysql not allow update table reading from, can temporary table.

create table temp select a.id, sum(b.event_type) tot  your_table  left join your_table b on b.id <= a.id group a.id;  update your_table,temp  set your_table.total = temp.tot  your_table.id = temp.id;  drop table temp;