Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to UPDATE using ROWNUM and ORDER BY
sandy_lucca_at_yahoo.com (Sandy) wrote in message news:<f2c5a2a1.0408100643.841f9eb_at_posting.google.com>...
> I am trying to do the following:
>
> EXEC SQL
> UPDATE MY TABLE
> SET COL1 = :newValue
> WHERE COL1 = 0
> AND ROWNUM = 1
> ORDER BY COL2;
>
> (index on COL1, COL2)
>
> Pro*C does not process the "ORDER BY" statement.
>
> How can I achieve the above
>
> Thanks
> Sandra
"order by" is not valid in an update command. 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);
Of course if the table is very large and there is no index on col2, this could be slow. Another alternative is to use a PL/SQL block.
DECLARE
CURSOR c IS
SELECT col1 FROM my_table
WHERE col1 = 0 ORDER BY col2 FOR UPDATE OF col1;
Hope this helps
Ken Denny
Received on Tue Aug 10 2004 - 14:03:01 CDT
![]() |
![]() |