problem with update query

Question

I have a query

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 rows of the table irrespective of RoWNUM <= 1000 i.e if i add

Table_1.BUS_ID = ft.BUS_ID

then it updates all rows that satisfies col2<> 1 AND col3<> 1 and ft.dupe_count=1. The table is having following structure:

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

Any idea why is it happening.Please help.

score 4 · Accepted Answer · answered Sep 05 '11 at 08:55

Niraj,

An ordinary subquery is evaluated for each table. A correlated subquery is evaluated for each row. And you have made the subquery in your second update statement correlated with the line Table_1.BUS_ID = ft.BUS_ID. And if it evaluates for each row, then it will always satisfy the ROWNUM <= 1000 predicate.

Regards,
Rob.

problem with update query

1 Answers1