Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> renumbering with rownum
Hi,
i have a table like
ID POS DATE FILTER 1 1 01.01.01 x 3 5 02.01.01 x 4 2 01.02.02 y 5 6 01.03.04 x 12 6 13.04.03 x
i try to renumber the column POS with plain sql, so that pos is incremental (order by pos, date where filter="x"). So it should look like
D POS DATE FILTER 1 1 01.01.01 x 3 2 02.01.01 x 4 2 01.02.02 y 5 4 01.03.04 x 12 3 13.04.03 x
I was successful with pl/sql and a cursor, but there has to be a way to do this by sql...
I tried:
UPDATE tbl a SET pos=
(
SELECT ROWNUM FROM (SELECT ID FROM TBL_PRO_ADD WHERE FILTER like 'x' ORDER BY pos, date) b WHERE b.id = a.id
but this led to every pos = 1, which is obvious because the correlating subquery contains only 1 record.
Any Hints?
Thank you
Stefan Received on Tue Feb 07 2006 - 07:46:20 CST