Update value equal to the count of not appearance on the last X records I have a MySQL table where a number between 0 and 99 is inserted every now an then. Every time a new value is inserted I need to count how many numbers between 0 and 99 does not appears in the last 100 previous records and update not_in_last_100_repetitions.

You can assume the structure of my table is:

num (int, primary, auto-increment) | winner (varchar) | not_in_last_100_repetitions (int)
Study the example below:

mysql&gt; create table test(id serial primary key, val int, cnt int);
Query OK, 0 rows affected (0.25 sec)

mysql&gt; insert into test (val)
    -&gt; select 10*rand()
    -&gt; from (
    -&gt; select 1 union all
    -&gt; select 2 union all
    -&gt; select 3 union all
    -&gt; select 4 union all
    -&gt; select 5 union all
    -&gt; select 6 union all
    -&gt; select 7 union all
    -&gt; select 8 union all
    -&gt; select 9 union all
    -&gt; select 10
    -&gt; ) dummy;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql&gt; select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
+----+------+------+
10 rows in set (0.00 sec)

mysql&gt; create trigger cnt_absent_in_last_10 before insert on test
    -&gt; for each row
    -&gt; set new.cnt = (select 10 - count(distinct val) from (select * from test order by id desc limit 10) dummy);
Query OK, 0 rows affected (0.07 sec)

mysql&gt; insert into test (val)
    -&gt; select 10*rand()
    -&gt; from (
    -&gt; select 1 union all
    -&gt; select 2 union all
    -&gt; select 3
    -&gt; ) dummy;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
| 16 |    8 |    3 | -- absent in 1..10: 4,5,8
| 17 |    4 |    2 | -- absent in 2..16: 4,5
| 18 |    6 |    2 | -- absent in 3..17: 5,7
+----+------+------+
13 rows in set (0.00 sec)

mysql&gt;