Sequential update SQL

Question

I have a table SGI01A in my database, where a column of records follows the following logic:

At some point, this ordination went out of order and began to count in a "wrong" way. What was to be 025/19 turned 031/19 and returned to correct sum.

How would the correct SQL query change all these values back to the order above without having to change one record at a time?

Answer 1

Friend, I believe that the easiest way to do this will be to use some programming language to compare the previous value with the current one and have a better control of the data.
Below is an example of how to do this using python:

import re
#Simulando o select com os dados do seu banco
valores = ['NIG-022/19','NIG-023/19','NIG-024/19','NIG-031/19','NIG-032/19','NIG-033/19']

sequencia = []
pattern = "(?<=NIG-)(.*)(?=/)"
# Utiliza expressão regular para extrair somente os números que devem estar em sequencia.
for atual in valores:
  x = re.search(pattern,atual)
  if x:
    sequencia.append(int(x.group()))

# Ordena a sequencia.
for i,numero in enumerate(sequencia):
#A partir da segunda posição compara se o numero atual é igual ao numero anterior +1, se não for, altera o mesmo para manter a sequencia.
  if i >0 and numero != (sequencia[i-1]+1):
    sequencia[i] = sequencia[i-1]+1

#Insere a sequencia ordenada nas strings
for i,atual in enumerate(valores):
  x = re.search(pattern,atual)
  if x:
    valores[i] = re.sub(pattern,str(sequencia[i]),valores[i])
#imprime as strings já ordenadas sequencialmente.
print(valores)

#Aqui você cria a lógia para dar update no seu BD.

If you want to test the example above, you can use this link