Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to UPDATE using ROWNUM and ORDER BY

Re: How to UPDATE using ROWNUM and ORDER BY

From: GQ <dbaguy_ott_at_yahoo.com>
Date: 11 Aug 2004 14:04:08 -0700
Message-ID: <aad8b5cb.0408111304.1bc0ac29@posting.google.com>


sandy_lucca_at_yahoo.com (Sandy) wrote in message news:<f2c5a2a1.0408110648.31d20a0a_at_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. Received on Wed Aug 11 2004 - 16:04:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US