Update and increment column by sorting (UPDATE and ORDER BY)

Asked

Viewed 226 times

3

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 by 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 a UPDATE to increase the value of REGISTO beginning 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 for 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.

mysql

1 answer

1


In your case it would be something more or less that way, having to adapt their 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

In case 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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.