Re: [Q] Sorting a column
From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 12 Aug 2008 17:19:15 -0700
Message-ID: <1218586746.595453@bubbleator.drizzle.com>
>>>> Hi
>>>>
>>>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
>>>> want to write an UPDATE query, which updates the column POS such that
>>>> its values correspond to the alphabetical order of NAME.
>>>>
>>>> UPDATE
>>>> T t1
>>>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
>>>> NAME)
>>>>
>>>> That does not work, because the WHERE clause returns a single row,
>>>> which will always have a ROWID of 1.
>>>>
>>>> How do I do this? (It's possible with a PROCEDURE, of course, but I
>>>> want to avoid them.)
Date: Tue, 12 Aug 2008 17:19:15 -0700
Message-ID: <1218586746.595453@bubbleator.drizzle.com>
Shakespeare wrote:
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht > news:1218223583.133026_at_bubbleator.drizzle.com... >> Shakespeare wrote: >>> "digory" <digory_at_gmx.net> schreef in bericht >>> news:1210a149-d43e-48a8-bbd7-9a688c96fa88_at_y38g2000hsy.googlegroups.com...
>>>> Hi
>>>>
>>>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER). I
>>>> want to write an UPDATE query, which updates the column POS such that
>>>> its values correspond to the alphabetical order of NAME.
>>>>
>>>> UPDATE
>>>> T t1
>>>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
>>>> NAME)
>>>>
>>>> That does not work, because the WHERE clause returns a single row,
>>>> which will always have a ROWID of 1.
>>>>
>>>> How do I do this? (It's possible with a PROCEDURE, of course, but I
>>>> want to avoid them.)
>>> ROWID? Don't you mean rownum? >>> I don't think tables will ever have rows with the same rowid (except by >>> coincidence) >>> >>> Shakespeare >> Not even by coincidence ... it is a technical impossibility. >> -- >> Daniel A. Morgan >> Oracle Ace Director & Instructor >> University of Washington >> damorgan_at_x.washington.edu (replace x with u to respond) >> Puget Sound Oracle Users Group >> www.psoug.org > > Just by curiousity (and too lazy to look in the manuals...): does Oracle > change ROWID's when a transportable tablespace is moved from one system to > an other? > > Shakespeare
I can't check it right now but it seems to me it would have no choice if it found a conflict. Whether it checks or just changes them I can not say without looking.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Aug 12 2008 - 19:19:15 CDT