mysql – Update and increment column through sorting (UPDATE and ORDER BY)

Question:

I have the following table

ID |    NOME     |  ANO  | REGISTO
----------------------------------
0       A          2015      4
1       B          2017      6
2       C          2014      15
3       D          2017      2
4       E          2013      55
5       F          2017      1
6       G          2017      6
7       H          2017      8

And doing the following query I get the result:

SELECT * FROM exemplo WHERE ano = 2017 ORDER BY nome ASC


ID |    NOME     |  ANO  | REGISTO
----------------------------------
1       B          2017      6
3       D          2017      2
5       F          2017      1
6       G          2017      6
7       H          2017      8

I intend to make an UPDATE that increments the value of the REGISTO starting with 1 according to the alphabetical order. That is:

ID |    NOME     |  ANO  | REGISTO
----------------------------------
1       B          2017      1
3       D          2017      2
5       F          2017      3
6       G          2017      4
7       H          2017      5

And repeating the process over the years, the end result would be:

SELECT * FROM EXEMPLO ORDER BY id

ID |    NOME     |  ANO  | REGISTO
----------------------------------
0       A          2015      1
1       B          2017      1
2       C          2014      1
3       D          2017      2
4       E          2013      1
5       F          2017      3
6       G          2017      4
7       H          2017      5

I'm questioning the process only through SQL, no programming language support.

Answer:

In your case, it would be something more or less like this, having to adapt to your real needs.

SET @prev := '';

SET @cnt := 1;

UPDATE exemplo e 
       JOIN (SELECT id, nome, IF(@prev <> ano, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := ano as prev
             FROM   exemplo 
             ORDER  BY ano, nome ASC) e1 
         ON e.id = e1.id 
SET    registo = e1.rank 

If you wanted to check the result before, you can run this query:

SET @prev := '';

SET @cnt := 1;

SELECT id, nome, IF(@prev <> ano, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := ano
FROM exemplo
ORDER BY ano, nome ASC
Scroll to Top