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.)

I just tried the following

UPDATE T
SET pos = DENSE_RANK () OVER (ORDER BY name)

Unfortunately, this does not work because Oracle does not allow
analytic functions in the SET clause.

I just tried the following

UPDATE T
SET pos = DENSE_RANK () OVER (ORDER BY name)

Unfortunately, this does not work because Oracle does not allow
analytic functions in the SET clause.

I just tried the following

UPDATE T
SET pos = DENSE_RANK () OVER (ORDER BY name)

Unfortunately, this does not work because Oracle does not allow
analytic functions in the SET clause.

I just tried the following

UPDATE T
SET pos = DENSE_RANK () OVER (ORDER BY name)

Unfortunately, this does not work because Oracle does not allow
analytic functions in the SET clause.

On Aug 7, 3:48*am, digory <dig... (AT) gmx (DOT) net> wrote:
This does:

declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t
set pos = gr.dr
where name = gr.name;
end loop;

commit;
end;
/

Here's proof:

SQL> select * From t;

NAME POS
-------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

NAME POS
-------------------- ----------
ADAMS
JAMES
FORD
MILLER

15 rows selected.

SQL> declare
2 cursor get_rank is
3 select name, dense_rank() over (order by name) dr
4 from t;
5 begin
6 for gr in get_rank loop
7 update t
8 set pos = gr.dr
9 where name = gr.name;
10 end loop;
11
12 commit;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t;

NAME POS
-------------------- ----------
SMITH 12
SMYTHE 13
ALLEN 2
WARD 15
JONES 7
MARTIN 9
BLAKE 3
CLARK 4
SCOTT 11
KING 8
TURNER 14

NAME POS
-------------------- ----------
ADAMS 1
JAMES 6
FORD 5
MILLER 10

15 rows selected.

SQL>


David Fitzjarrell

On Aug 7, 3:48*am, digory <dig... (AT) gmx (DOT) net> wrote:
This does:

declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t
set pos = gr.dr
where name = gr.name;
end loop;

commit;
end;
/

Here's proof:

SQL> select * From t;

NAME POS
-------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

NAME POS
-------------------- ----------
ADAMS
JAMES
FORD
MILLER

15 rows selected.

SQL> declare
2 cursor get_rank is
3 select name, dense_rank() over (order by name) dr
4 from t;
5 begin
6 for gr in get_rank loop
7 update t
8 set pos = gr.dr
9 where name = gr.name;
10 end loop;
11
12 commit;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t;

NAME POS
-------------------- ----------
SMITH 12
SMYTHE 13
ALLEN 2
WARD 15
JONES 7
MARTIN 9
BLAKE 3
CLARK 4
SCOTT 11
KING 8
TURNER 14

NAME POS
-------------------- ----------
ADAMS 1
JAMES 6
FORD 5
MILLER 10

15 rows selected.

SQL>


David Fitzjarrell

On Aug 7, 3:48*am, digory <dig... (AT) gmx (DOT) net> wrote:
This does:

declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t
set pos = gr.dr
where name = gr.name;
end loop;

commit;
end;
/

Here's proof:

SQL> select * From t;

NAME POS
-------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

NAME POS
-------------------- ----------
ADAMS
JAMES
FORD
MILLER

15 rows selected.

SQL> declare
2 cursor get_rank is
3 select name, dense_rank() over (order by name) dr
4 from t;
5 begin
6 for gr in get_rank loop
7 update t
8 set pos = gr.dr
9 where name = gr.name;
10 end loop;
11
12 commit;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t;

NAME POS
-------------------- ----------
SMITH 12
SMYTHE 13
ALLEN 2
WARD 15
JONES 7
MARTIN 9
BLAKE 3
CLARK 4
SCOTT 11
KING 8
TURNER 14

NAME POS
-------------------- ----------
ADAMS 1
JAMES 6
FORD 5
MILLER 10

15 rows selected.

SQL>


David Fitzjarrell

On Aug 7, 3:48*am, digory <dig... (AT) gmx (DOT) net> wrote:
This does:

declare
cursor get_rank is]
select name, dense_rank() over (order by name) dr
from t;
begin
for gr in get_rank loop
update t
set pos = gr.dr
where name = gr.name;
end loop;

commit;
end;
/

Here's proof:

SQL> select * From t;

NAME POS
-------------------- ----------
SMITH
SMYTHE
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

NAME POS
-------------------- ----------
ADAMS
JAMES
FORD
MILLER

15 rows selected.

SQL> declare
2 cursor get_rank is
3 select name, dense_rank() over (order by name) dr
4 from t;
5 begin
6 for gr in get_rank loop
7 update t
8 set pos = gr.dr
9 where name = gr.name;
10 end loop;
11
12 commit;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t;

NAME POS
-------------------- ----------
SMITH 12
SMYTHE 13
ALLEN 2
WARD 15
JONES 7
MARTIN 9
BLAKE 3
CLARK 4
SCOTT 11
KING 8
TURNER 14

NAME POS
-------------------- ----------
ADAMS 1
JAMES 6
FORD 5
MILLER 10

15 rows selected.

SQL>


David Fitzjarrell

digory <digory (AT) gmx (DOT) net> wrote:
update t
set pos =
(select new_id
from (select rowid, dense_rank() over (order by name) new_id
from t) t2
where t2.rowid = t.rowid);

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."