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.