Re: How to UPDATE using ROWNUM and ORDER BY

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 16 Aug 2004 10:56:24 -0700
Message-ID: <4b5394b2.0408160956.6473fbb6@posting.google.com>

kk2796_at_hotmail.com (Kevin) wrote in message news:<5a1dc659.0408131058.78d2362a_at_posting.google.com>...
> UPDATE MY_TABLE
> SET COL1 = :NEWVALUE
> WHERE ROWID =
> (SELECT ROWID
> FROM MY_TABLE
> WHERE COL1 = 0
> AND ROWNUM = 1
> ORDER BY COL2)
Kevin,

Since rhe pseudo-column ROWNUM is assigned BEFORE any ORDER BY clause, the above query does not do what you seem to think it does.

<rant>
And my standard question about use of ROWNUM applies:

WHY USE IT? If you really have duplicate rows (all columns identical values), then removing all but one seems to be better than faking differences in rows based on the arbtrary order of retrieval. If they aren't really duplicate rows, then use the other columns that aren't identical to correctly sort out the ONE row you really want. (then get a proper primary key created on that table ASAP)

But if you really must do something like this, what's wrong with a little PL/SQL block? (and don't forget the FIRST_ROWS hint to the optimizer)
</rant>

Sorry, but I just really dislike ROWNUM and it's abuses.

Ed Received on Mon Aug 16 2004 - 12:56:24 CDT