I need to update a table in blocks of 100. That is, update 100 first, then another 100 after two days, and so on until the total number of records in the table is completed.
The problem is that I need to update the records by date in a descending way. I have tried to do it with the following queries:
Update top (100) DUMMYTABLE
set ESTADO = 3
where ESTADO = 5 order by FECHACREACION desc
Which gives wrong syntax.
I have also tried:
Update top (100) DUMMYTABLE
set ESTADO = 3
where ESTADO = 5 and id in (select top(100) * from DUMMYTABLE where ESTADO = 5 order by FECHACREACION desc)
But it gives me the following error message: An expression can only be specified in the select list when the subquery is not specified with EXISTS.
I have found this accepted answer in English so, but it gives me the same error as when launching the second query.
Thanks in advance!
You could try using sql-server's Common Table Expressions as follows:
Create a NULL-initialized update_date field in the DUMMYTABLE table, each time you update a record that updates with the current date GETDATE(). Then create a temporary table with the 100 records you want to update and filter those with the date_update field set to NULL.
You then update the field by joining the temporary table with the DUMMYTABLE table by its key.