How to perform UPDATE with ORDER BY on sql server?

I would like to perform an update ordering the results of it, so I wanted to run an update like this:

UPDATE pessoa SET nome = 'Jose' 
WHERE sobrenome LIKE '%Betalla%'
ORDER BY nome

But when I try to perform this update, an incorrect syntax error occurs near the “ORDER”.

I would like to order by to get the first result of the ordered query.

To use ORDER BY in update, you need to use a subquery and sort it with OVER clause, for example:

;WITH pessoaAux AS(
  SELECT Id, Nome, Idade, ROW_NUMBER() over(order by nome asc) rowNumber
  FROM pessoa 
)
UPDATE pessoa SET Ordem = rowNumber
FROM pessoa p
INNER JOIN pessoaAux pAux on p.Id = pAux.Id

You can also perform ORDER BY in the subquery, but you must have the TOP, OFFSET, or FOR XML clause, as in the example:

;WITH pessoaAux AS(
  SELECT TOP 10 Id, Nome, Idade
  FROM pessoa 
  ORDER BY Nome
)
UPDATE pessoa SET nome = 'novo valor do nome'
FROM pessoa p
INNER JOIN pessoaAux pAux on p.Id = pAux.Id

Based on your comment, you can force an update based on an ordered query, but for this you will need to use a cursor.

Curosr will make your update slower.

Your script will look something like this

declare @id nvarchar(4000)
declare pap_log cursor read_only forward_only for 


select id from  pessoa 
WHERE sobrenome LIKE '%Betalla%'
ORDER BY nome

open pap_log
fetch next from pap_log into @id
while @@fetch_status = 0
begin 

UPDATE pessoa SET nome = 'Jose' 
WHERE id =@id

fetch next from pap_log into @id
end
close pap_log
deallocate pap_log

Anything questions there

UPDATE com ORDER BY

Regarding the issue raised in the title: There is ORDER BY in SQL UPDATE comando . Postgres refreshes rows in arbitrary order. But you have (limited) options to decide whether the constraints are checked after each line, after each statement or at the end of the transaction. You can avoid duplicate key violations for intermediate states with DEFERRABLE restriction.

See more details here.