Re: How to UPDATE using ROWNUM and ORDER BY

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

You need the "where col1 = 0" both places. This will work:

UPDATE my_table
  set col1 = :newValue
  where rowid =

       (select rowid from my_table
          where col1 = 0
            and col2 =
               (select min(col2) from my_table
                  where col1 = 0)
            and rownum = 1);

Ken Received on Fri Aug 13 2004 - 12:03:38 CDT