Search code examples
oracle-databasesortingrownum

oracle sql. how to manage ORDER BY and ROWNUM

I get data from a table by this query

SELECT ID, .... FROM TAB WHERE ROWNUM<100 ORDER BY ID;

then, I should mark rows as read:

UPDATE TAB SET READ = '1' WHERE ID IN (SELECT ID FROM TAB WHERE ROWNUM<100);

checking data, I found out that marked rows are not the same retrieved by the first query. I tried to force sorting in update query, but

UPDATE TAB SET READ = '1' WHERE ID IN (SELECT ID FROM TAB WHERE ROWNUM<100 ORDER BY ID);

does not work. how can I get the marked rows will be the same as the first query?

Solution

  • The ordering is done after applying the ROWNUM<100, leaving you with inconsistent results. To change this order you can either do:

    UPDATE TAB 
SET READ = '1' 
WHERE ID IN (
  SELECT ID 
  FROM TAB 
  ORDER BY ID 
  FETCH FIRST 99 ROWS ONLY
);

    Or:

    UPDATE TAB 
SET READ = '1' 
WHERE ID IN (
  SELECT ID FROM (
    SELECT ID 
    FROM TAB 
    ORDER BY ID
  ) 
  WHERE ROWNUM <100
)