Re: How to UPDATE using ROWNUM and ORDER BY

Thank you all for your help, I have one further question.

> It appears that what you want to do is to update the row having the
> lowest value for col2 where col1=0. You could use this:
> UPDATE MY_TABLE
> SET col1 = :newValue
> WHERE col1 = 0
> AND col2 =
> (SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0);

Yes, That is what I was trying do and only update a single row (in one statement).

Could I do something like this:

UPDATE MY_TABLE
  SET col1 = :newValue
  WHERE col2 =
    (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1 AND col1 = 0);

If this is OK, I was wondering what are the performance implications of moving the "WHERE" clause to the outer update statement (index is on col2 as well), i.e.:

UPDATE MY_TABLE
  SET col1 = :newValue
  WHERE col1 = 0
  AND col2 =
    (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1);

Thanks
Sandra Received on Wed Aug 11 2004 - 09:48:27 CDT