Re: How to UPDATE using ROWNUM and ORDER BY

From: Ken Denny <ken_at_kendenny.com>
Date: 10 Aug 2004 12:03:01 -0700
Message-ID: <ba944bc3.0408101103.397e98cb@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 - 14:03:01 CDT