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