Well, what I'm trying to do is a query so that when a record is deleted from the "class" table, the value of the "counter" column of the other records is subtracted 1 as long as the id of the records is greater than the id of the record that I deleted. Then I will list that table on a page and the columns I would use would be:
contador , nClase
Since if I show the idClase and delete records the numbers come out in disarray:
1, 5, 10, 11, 12, 20, etc
The counter column in this case is totally unnecessary, it is nothing more than a calculated field that does not contribute anything and will also bring you headaches since you can not guarantee the consistency of the data, if you need to number the records returned in a query you can add a counter on the flight:
Example:
SELECT a.*, (@rownum:=@rownum+1) contador
FROM (SELECT @rownum:=0) t, 'mi_tabla' a;
You can see a working example here
Complete example:
CREATE TABLE IF NOT EXISTS 'mi_tabla' (
'id' int(6) unsigned NOT NULL,
'rev' int(3) unsigned NOT NULL,
'content' varchar(200) NOT NULL,
PRIMARY KEY ('id')
) DEFAULT CHARSET=utf8;
INSERT INTO 'mi_tabla' ('id', 'rev', 'content') VALUES
('1', '1', 'text 1'),
('2', '1', 'text 2'),
('6', '2', 'text 3'),
('9', '3', 'text 4');
SELECT a.*, (@rownum:=@rownum+1) contador
FROM (SELECT @rownum:=0) t, 'mi_tabla' a
ORDER BY a.id;
Result:
| id | rev | content | contador |
|----|-----|---------|----------|
| 1 | 1 | text 1 | 1 |
| 2 | 1 | text 2 | 2 |
| 6 | 2 | text 3 | 3 |
| 9 | 3 | text 4 | 4 |