Update value equal to the count appearance on the last X records I have a MySQL table everyday time a new value is inserted I need to count look at the last 100 previous records and count how many times the inserted value appears.

You can assume the structure of my table is:

num (int, primary, auto-increment) | value (varchar) | last_100_repetitions (int)


I need to update last_100_repetitions based on how many times value appears on the last 100 inserted records (where num indicates the order of the records).
I find the construction rather error prune, what if a row is deleted, or value is updated, should last_100_repetitions also be updated then? That said, a trigger like:

delimiter //
create trigger trg 
before insert on t 
for each row 
    set new.last_100_repetitions = (select count(1) 
                                    from ( 
                                        select value 
                                        from t 
                                        order by num desc 
                                        limit 100 
                                    ) as x 
                                    where value = new.value ) 
//
delimiter ;


may work. If you are only interested in the last last_100_repetitions for each value you may concider moving this attribute to a separate table.

Test:

MariaDB [test3]&gt; insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]&gt; select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
+----+-------+----------------------+
4 rows in set (0.01 sec)

MariaDB [test3]&gt; insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]&gt; select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
| 16 | A     |                    2 |
| 17 | A     |                    3 |
| 18 | B     |                    1 |
| 19 | C     |                    1 |
+----+-------+----------------------+
8 rows in set (0.00 sec)