Problem with update request

Question

I have a request

UPDATE Table_1 SET Col1='Y' WHERE ROWID IN ( select ROWID from ( SELECT BUS_ID, row_number() over (partition by BUS_ID order by BUS_ID) dupe_count, rowid from Table_1 WHERE col2 <> 1 AND col3 <> 1 order by dbms_random.value ) ft where ft.dupe_count = 1 AND ROWNUM <= 1000 ); 

updates only 1000 rows in table Table_1.

But if I write

  UPDATE Table_1 SET Col1='Y' WHERE ROWID IN ( select ROWID from ( SELECT BUS_ID, row_number() over (partition by BUS_ID order by BUS_ID) dupe_count, rowid from Table_1 WHERE col2 <> 1 AND col3 <> 1 order by dbms_random.value ) ft where ft.dupe_count = 1 and Table_1.BUS_ID = ft.BUS_ID AND ROWNUM <= 1000 ); 

it updates all the rows of the table regardless of RoWNUM <= 1000 ie if I add

 Table_1.BUS_ID = ft.BUS_ID 

then it updates all rows col2<> 1 AND col3<> 1 and ft.dupe_count=1 . The table has the following structure:

 BUS_ID | col1 | col2 | col3 1 | | 0 | 0 2 | | 0 | 0 1 | | 0 | 0 3 | | 1 | 1. 

Any idea why this is happening. Please help.

Answer 1

Niraj,

A regular subquery is evaluated for each table. The correlation subquery is evaluated for each row. And you made a subquery in your second update statement, correlated to the Table_1.BUS_ID = ft.BUS_ID . And if it evaluates each row, it will always satisfy the predicate ROWNUM <= 1000 .

Yours faithfully,
Rob