2

I am using PostgreSQL and let's say I have a tasks table to keep track of task items. Tasks table is as follows;

Id    Name      Index
7     name A      1
5     name B      2
6     name C      3
3     name D      4

Index column in tasks table stores the sort order of the tasks. Therefore I will output the tasks with respect to index in increasing order.

So When I change Task D(id = 3)' s index into 2 the new indexes should be as below;

Id    Name      Index
7     name A      1
5     name B      3
6     name C      4
3     name D      2

or when I change Task A(id = 7)' s index into 4 the new indexes should be as below;

Id    Name      Index
7     name A      4
5     name B      2
6     name C      3
3     name D      1

What I think is updating all row's index values one by one is pretty inefficient.

So what is the most efficient way to update all index values when I change one of the indexes in my Tasks table? 

Edit :

First of all sorry for the confusion. What I am asking is not a simple exchanging two row indexes. If you look at the examples when I change Task D's index in to 2 more than one rows change. So when Task D is 2, Task B becomes 3 and Task C becomes 4.

For instance;

It is like when you drag Task D and drop below Task A so that it's index becomes 2 and B and C's index increases by 1.

yns
  • 440
  • 2
  • 8
  • 28
  • 1
    This looks like maintainig a "tabbing order". See my answer here http://stackoverflow.com/a/14092775/905902 for doing this automatically, using a trigger function. – wildplasser Aug 16 '14 at 11:41

2 Answers2

0

SQL Fiddle

What you are doing is exchanging two row's indexes. So it is necessary to store the index value of the first updated one in a temp variable and setting it temporarily to a special value to avoid a unique index collision, that is, if the index is unique. If the index is not unique that step is unnecessary.

begin;
create temp table t as
select
(
    select index
    from tasks
    where id = 3
) as index,
(
    select id
    from tasks
    where index = 2
) as id
;

update tasks
set index = -1
where id = (select id from t)
;
update tasks
set index = 2
where id = 3
;
update tasks
set index = (select index from t)
where id = (select id from t)
;
drop table t;
commit;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Sorry but what I am asking is not exchanging two row's indexes. I edited my question. – yns Aug 16 '14 at 00:33
0

The following assumes the index column (as well as id) is unique:

with swapped as (
  select n1.id as id1,
         n1.name as name1,
         n1.index as index1,
         n2.id as id2,
         n2.name as name2,
         n2.index as index2
  from names n1
    join names n2 on n2.index = 2 -- this is the value of the "new index" 
  where n1.id = 3 -- this is the id of the row where the index should be changed to the new value 
)
update names as n
  set index = case 
              when n.id = s.id1 then s.index2
              when n.id = s.id2 then s.index1
            end
from swapped s
where n.id in (s.id1, s.id2);

The CTE first creates a single row with the ids of the two rows to be swapped and then the update just compares the ids of the target table with those from the CTE, swapping the values.

SQLFiddle example: http://sqlfiddle.com/#!15/71dc2/1