Update to numbering in column order

Asked

Viewed 817 times

0

Good afternoon! I am with a situation in my service, I am beginner in sql server and would like a help or tip if possible. I have some records (54) of a table that need to be changed its code ordering from 1 to 54. I tried using to declare a float variable, but when running in the database all the records of the table in the column "CODIGOVENDA" were changed to numbering "1". I am using the query below:

DECLARE @CONTADOR FLOAT 
DECLARE @MAXIMO FLOAT 
SET @CONTADOR = 0 SET @MAXIMO = 54 
WHILE @CONTADOR < @MAXIMO 
UPDATE VENDAS SET CODIGOVENDA = @CONTADOR + 1

Could someone give me a strength on how to perform this procedure correctly, please

sql sql-server

  • Do you have any columns to sort? A sale date or something?

  • Very strange the way you are doing, it is not the usual way to work with SQL, but, I believe, somewhere in your process you have to increment the variable @COUNTER. The way it is, everything points to an infinite loop.

2 answers

1


Utilize ROW_NUMBER:

UPDATE x
   SET x.CODIGOVENDA = x.NOVO_CODIGOVENDA
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY CODIGOVENDA) AS NOVO_CODIGOVENDA
      FROM VENDAS V
  ) x
 WHERE x.CODIGOVENDA IS NOT NULL;

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

0

Danilo, if this has not generated an infinite loop, you should have another line inside while adding @COUNTER to itself plus one:

SET @CONTADOR = @CONTADOR + 1

Another important point, is that your update will always do the SET for all table records, you need to check if you have any condition ( WHERE ) that can be done, otherwise, always after execution, all records will have the CODE field equal.

Browser other questions tagged sql sql-server

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