sort order column
Hello, I have a table with a column named SortOrder wich is used for sorting (obviously) when fething a result set. I'm trying to figure out how to wirite a query to easily change theese values.
Say I have 6 rows
create table myTable
( id number,
name varchar2,
sortOrder number);
insert into myTable values(1, 'abc', 1);
insert into myTable values(2, 'abcd', 2);
insert into myTable values(3, 'ab', 3);
insert into myTable values(4, 'abcabc', 4);
insert into myTable values(5, 'a', 5);
insert into myTable values(6, 'b', 6);
and I want to move the fifth row up a bit, say to sortOrder=2, this would mean I would have to change the sortOrder-value on all rows from 2 through 5.
the result I want is:
1, abc, 1
5, a, 2
2, abcd, 3
3, ab, 4
4, abcabc, 5
6, b, 6
Can I achieve this i a single query (or procedure)?