Re: [Q] Sorting a column
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 12 Aug 2008 12:33:23 +0200
Message-ID: <48a166f4$0$196$e4fe514c@news.xs4all.nl>
>> "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
Date: Tue, 12 Aug 2008 12:33:23 +0200
Message-ID: <48a166f4$0$196$e4fe514c@news.xs4all.nl>
"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 Received on Tue Aug 12 2008 - 05:33:23 CDT