Re: [Q] Sorting a column
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 13 Aug 2008 07:55:27 +0200
Message-ID: <48a27779$0$195$e4fe514c@news.xs4all.nl>
>>>>> 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: Wed, 13 Aug 2008 07:55:27 +0200
Message-ID: <48a27779$0$195$e4fe514c@news.xs4all.nl>
"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1218586746.595453_at_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.org
So there is still a SLIGHT possibility two tables may have rows with a duplicated rowid (by coincidence) after all?
Shakespeare Received on Wed Aug 13 2008 - 00:55:27 CDT