Question:
I would like to perform an update ordering its results, for that I would like to run an update like this:
UPDATE pessoa SET nome = 'Jose'
WHERE sobrenome LIKE '%Betalla%'
ORDER BY nome
But when I try to run this update there is an incorrect syntax error near the "ORDER".
I would like to perform the order by to get the first result of the ordered query.
Answer:
Based on your William comment you can indeed force an update based on an ordered query, but for that you will need to use a cursor.
Curosr will make your update slower.
Your script will look 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, ask there