sa*********@yahoo.com (Sandy) wrote in message news:<f2**************************@posting.google. com>...
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
1- Rownum doesn't buy you anything, other then ending the inner select
after retreiving one row. That row can be any row within your table -
due to the random retreival by Oracle (will probably be the first
physical row in the table, most of the time).
With that - returning only one row - why request the min(col2) - since
you only have one row ?
2- What would almost make more sense is moving the 'rownum=1'
condition to the outer SQL statement (update), but there again you are
selectively picking the first row, when you don't know the order that
multiple rows may be returned.
FYI - rownum is not a row id and can not be counted on to identify a
specific row, other then the position that the row is returned with
the return set.
Furthermore, if you don't specify an 'order by clause' the rows can be
in any order.