Re: How to UPDATE using ROWNUM and ORDER BY
Date: 10 Aug 2004 12:03:01 -0700
Message-ID: <ba944bc3.0408101103.397e98cb_at_posting.google.com>
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;
r c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO r;
IF c%FOUND THEN
UPDATE my_table SET col1 = :newValue WHERE CURRENT OF c;
END IF;
CLOSE c;
END; An advantage here is that if the min(col2) value is in multiple rows with col1 = 0, the first example will update all of them while the second example will only update one. No way to tell which one though.
Hope this helps
Ken Denny
Received on Tue Aug 10 2004 - 21:03:01 CEST