How to do update with count in SQL Server?

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 the base:

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

To perform update , simply use the ROW_NUMBER to sort your table according to the desired field, thus getting update :

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

I think this is what you need (I can not test):

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

You can use the ROW_NUMBER () function to do this count. Here is an example query 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