How to set an item in a query to To Row number

I have a form that is generated by a query. I want to press a batten and number a column from 1 to N sequentially. Say from top to bottom.
I guess the best way is to write a macro that does that. Does anyone knows how to do that?
Alternatively, one can run a SQL query to do that. I found this SQL for Access that does this:

WITH UpdateCTE
AS
(SELECT item_no,
ROW_NUMBER() OVER(ORDER BY item_no) AS new_value
FROM WO_Items)
UPDATE UpdateCTE
SET item_no = new_value;

Would this, or similar code, works in Base?

Let me explain my database that I am creating a little more to make it easier for people to understand it better.
I have built a database of tasks to be done. These Tasks are in Table “T_Task”

Table “T_Task” have following fields:
“ID” which is Primary Key
“Task” which is the task to be done
“Today_Order” which is the order to be done Today{ When a task is selected to be done today, it would get a decimal number <100}
“Status_Finished” which is status of the task
“Importance” which is importance of the task within its own category,
“Catagory” which is a foreign key pointing to Primary Key of Category table

Now the tasks that are selected to be done today will get a real number from 0 to 100 in their “Today_Order” field.
Then a form runs a Query that selects only the records that in their “Today_Order” is a number less than 100. This is the Query that does this:

SELECT "T_Task"."ID", "T_Task"."Task", "T_Task"."Today_Order", "T_Task"."Status_Finished", "T_Task"."Importance", "T_Task"."Catagory", "T_Catagory"."ID" "ID1", "T_Catagory"."Catagory" FROM { OJ "T_Catagory" RIGHT OUTER JOIN "T_Task" ON "T_Catagory"."ID" = "T_Task"."Catagory" } 
  WHERE "T_Task"."Today_Order" <= 100.00 ORDER BY "T_Task"."Today_Order" ASC

At this point I renumber the “Today_Order” of these records so I know which tasks to be done sooner or later.
If you have more than 20 tasks to be done in a day, you can not order all of them in one take. You order a few by giving their “Today_Order” a number, refresh the form and place other tasks in between these tasks by giving them a number between the already ordered tasks. As you proceed you get to situations where these numbers become to close together, and you need to go to many decimal points to place a task between them.

A this point I start renumbering the tasks which is a tedious chore if you have more than say 20 tasks. That is why I want to renumber them automatically.

Hi

We can avoid a loop creating a counter (named PYS in the example) in the SQL and assigning its value to a field (named Number in the example):

oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQueryUpdate = oConn.CreateStatement()

qStr = "DROP SEQUENCE ""PYS"" IF EXISTS "
qStr = qStr & "CREATE SEQUENCE ""PYS"" AS INTEGER START WITH 1 "
qStr = qStr & "UPDATE ""Table1"" SET ""Number"" = NEXT VALUE FOR ""PYS"""

oQueryUpdate.executeUpdate(qStr)

Regards

If you already are doing a macro, this is simple enough. Simply calculate the value in the macro and pass it to an UPDATE (or INSERT) query in a loop. Here is an example for “Table1” where “priKey” is the primary key and “criteriaA” is common to all of the rows that you wish to re-number.

Sub sqlOperation

REM #! create two cursors:  one to read recordset, other to execute updates
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQueryResultSet = oConn.CreateStatement()
oQueryUpdate = oConn.CreateStatement()

REM #! read recordset into cursor based on criteria:  these are the rows to update
qStr = "SELECT ""priKey"" FROM ""Table1"" WHERE ""criteriaA"" = 'q' "
resultSet = oQueryResultSet.executeQuery(qStr)

REM #! read each row of recordset
Do While resultSet.isLast() = False
  resultSet.next
  rowNum = resultSet.getRow
  priKeyNo = resultSet.getString(1)

  REM #! use recordset row number and primary key to execute UPDATE query
  qStr = "UPDATE ""Table1a"" SET ""item_no"" = " & rowNum & " WHERE ""priKey"" = " & priKeyNo & ";"
  oQueryUpdate.executeUpdate(qStr)

  REM #! iterate down recordset, which is object not array, loop until last row of recordset
  i = i + 1

Loop

End Sub

That will update existing rows on a table, which it will pull based on the matching value in column CriteriaA. The overall context of your objective is missing, and undoubtedly if you are planning to change the values in tables that are loaded in forms there will be the obligatory reload and other Form operations to repaint the contents.

Alternately, you could iterate through the records on the subform (assuming you have a subform) and change the values though the subform rather than directly on a table.

The query you propose probably will not work because ROW_NUMBER() and OVER appear unsupported in HSQLDB, and some other databse engines too. Creating a list of numbers via a SQL query often requires an existing list (in MySQL at least) and in any event is not so trivial to do with the limited information about your tables that is disclosed in the question.

EDIT: added annotations per comment

(if this answers your question, please accept thie answer by clicking the check (image description) to the left).

Thank you for answering my question.
I am sure this answer will be used by others who search for this question, so could you explain what each line is doing, so It would be more educational to new users. I will include your answer in my own answer to my own question, which will summarize and explain in more detail the steps to be taken.
Thanks Again.