How to update with SQL Server count?

Question

I have the following table:

Nome   | Posicao
Item1  | NULL 
Item2  | NULL
Item3  | NULL

I’d like her to be like this:

Nome   | Posicao
Item1  | 1 
Item2  | 2
Item3  | 3

The ordering criterion is Name

Answer 1

I think this is what you need (I have no way to test):

DECLARE @contador int;
SET @contador = 1;
UPDATE tabela SET @contador = Posicao = @contador + 1 ORDER BY Nome;

I put in the Github for future reference.

Answer 2

You can use the function ROW_NUMBER() to do this count. Follow query example below.

WITH ALGUMNOME AS (
                   SELECT 
                           NOME AS 'NOME',
                           ROW_NUMBER() OVER (ORDER BY NOME DESC) RN
                   FROM 
                           TABELA
                   ORDER BY
                           NOME
                   )

UPDATE TABELA SET POSICAO = RN FROM TABELA
INNER JOIN ALGUMNOME ON ALGUMNOME.NOME = TABELA.NOME

Answer 3

Let’s say you have the following table:

CREATE TABLE [dbo].[Pessoa](
[nome] [varchar](50) NULL,
[posicao] [int] NULL
)

For this, we will use this insert as a basis:

insert into pessoa values 
('Mariana', 1),
('Joao', 2),
('Maria', 3),
('Paula', 4),
('Marcos', 5),
('Ana', 6),
('Pedro', 7)

To accomplish the update, just use the function ROW_NUMBER to sort your table according to the desired field, thus update:

UPDATE Pessoa 
SET Posicao = new_posicao
FROM (SELECT Posicao, ROW_NUMBER() OVER (ORDER BY Nome) 
                      AS new_posicao FROM Pessoa)
Pessoa