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