4
I would like to perform an update ordering the results of the same, for this 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 there is an incorrect syntax error near "ORDER".
I would like to order by to get the first result of the ordered query.
4
Based on your comment William you can force an update based on an ordered query, but for that you will need to use a cursor.
The curosr will slow down your update.
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 you ask there
4
To use ORDER BY in the update, you need to use a subquery and sort it with the 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
It is also possible to perform the ORDER BY in the subquery, but it is necessary to have the clause TOP, OFFSET or FOR XML, 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
-1
UPDATE com ORDER BY
On the question raised in the title: There is no ORDER BY in a SQL UPDATE comando. Postgres updates lines in arbitrary order. But you have options (limited) to decide whether restrictions are checked after each row, after each statement or at the end of the transaction. You can avoid duplicate key breaches for intermediate states with DEFERRABLE restriction.
Browser other questions tagged sql sql-server order-by sql-update
You are not signed in. Login or sign up in order to post.
But the
UPDATEdoes not return anything, as hell you want to use theORDER BY? the.0– DH.
One of the reasons is update does not return records, it can even return the number of affected lines which is something totally different ;)
– rray
You can select with this query after the update or generate N updates with a select so you can get the id of all lines that will be changed.
– rray
I wanted to order the order in which the update will be carried out, would have how to accomplish this?
– William
Update will not return records. What could be is an [tag:Insert] with [tag:select] already sorted.
– Eduardo Mendes
@William Why? It makes no sense that you need to order how the command will happen. In the end the result is the same.
– DH.
@Eduardomendes in the case I think I need an update, so an update with select already sorted would solve?
– William
I think the closest would be, like,
SELECT +'UPDATE .... WHERE sobrenome like \'%abc\'+ id FROM tabela ORDER BY algo– rray
@DH. has a case here that this ordering would alter the final result, so.
– William
@William Only situation that I can imagine this is trying to update a Primary Key being that the table points to itself, but there is a good reason to exist Constraint that prevents the value of a PK change
– DH.
@William, I saw a solution for update with select, take a look at this link and see if it helps you
– Eduardo Mendes