Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

help with select/update?

474368Jun 15 2007 — edited Jun 18 2007
Basically I want to have table to behave like the "netflix" movie queue priority where a user can update a row ( or any number of rows) in the queue to be any priority and the queue sorts out the new order of the queue.

Can this be done with a select?

Say I have a table with 3 columns Such as:
prio	new_prio	name

1	1		First record
2	2		Second record
3	3		Third Record
4	4		Forth Record
5	5		Fifth Record
6	6		Sixth Record
What I want to happen is that the prio, to always be sequence, starting at 1, incrementing for each record for the whole table.

So if I took the same table and updated the new_prio as follows:
prio	new_prio	name

1	1		First record
2	2		Second record
3	2		Third Record  <- updated this record  new_prio = 2
4	4		Forth Record
5	3		Fifth Record  <- updated this record  new_prio = 3
6	6		Sixth Record
I would want to update the prio column to follow the updates from the new_prio column So the results would look like (sorted on prio):
prio	new_prio	name

1	1		First record
2	2		Third Record
3	3		Fifth Record
4	4		Second record
5	5		Forth Record
6	6		Sixth Record
Now say I delete the two records like:
prio	new_prio	name

1	1		First record
2	2		Third Record  <- delete this one 
3	3		Fifth Record  <- and this one
4	4		Second record
5	5		Forth Record
6	6		Sixth Record
I would expect the following:
prio	new_prio	name

1	1		First record
2	2		Second record
3	3		Forth Record
4	4		Sixth Record
Is there a way to get this done in a simple select or update? what about a simple cursor loop?

I know I could do this with a lot of pl/sql code, But I am very interested if this could be done in sql. I have come close with using a cursor loop with inline views using rownum, but I am hitting problems with moving more then 2 rows around. when updating multiple rows, some rows come out one or two rows out of their intended order/prio.

Thanks in advance.

--pat

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 16 2007
Added on Jun 15 2007
15 comments
1,928 views