CODEWITHSUNDEEP
mysql
kojow7
kojow7

Reputation: 11394

Reorder items in mysql

I have a MySQL table that has the following columns: (id, position, etc.). Position indicates a sort order. How can I rearrange position of items. For example, if I want to rearrange the item at position 3 to position 5 or vice-versa, how can I achieve this?

As requested here is some sample data:

id  position name
==  ======== ======
1       5     Fred
2       3     Wilma
3       1     Betty
4       2     Barney
5       4     Pebbles

So if I do a select * from customer order by position I get:

id position name
== ======== =======
3      1     Betty
4      2     Barney
2      3     Wilma
5      4     Pebbles
1      5     Fred

I might then want to move Fred up in the list, say to position 3. I would then like the list to look like this:

id position name
== ======== =======
3      1     Betty
4      2     Barney
1      3     Fred
2      4     Wilma
5      5     Pebbles

My question is, how do I move an item either up or down in the list and get the positions to adjust accordingly?

I guess it would also be important to make sure that there are no missing or repeated position numbers.

Upvotes: 4

Views: 351

Answers (3)

Aniket Kariya
Aniket Kariya

Reputation: 1970

you can create a stored procedure to update position:

DELIMITER //
CREATE PROCEDURE simpleproc (IN param1 INT, IN param2 INT)
BEGIN
   UPDATE sampleTable SET position = position + 1 WHERE position >= param2;
   UPDATE sampleTable SET position = param2 WHERE id = param1;
END;//
DELIMITER ;

Just make sure that position is not set as primary key, i know you want it unique; but it can cause problems with procedure.

btw, param1 is id of record which you want to update and param2 is position that you want to set.

enter image description here enter image description here enter image description here

It works just fine.

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

In this UPDATE example the pos_from and pos_to have to be set.

The position with pos_from will be changed to pos_to.
And the others that need to shift up or down are corrected with +1 or -1 accordingly.

The pos_from can be higher or lower than the pos_to.

create table bedrock (
  id int primary key auto_increment, 
  position int not null, 
  name varchar(30) not null
);

insert into  bedrock (position, name) values
(5, 'Fred'),
(3, 'Wilma'),
(1, 'Betty'),
(2, 'Barney'),
(4, 'Pebbles');

select * from bedrock order by position;

id | position | name   
-: | -------: | :------
 3 |        1 | Betty  
 4 |        2 | Barney 
 2 |        3 | Wilma  
 5 |        4 | Pebbles
 1 |        5 | Fred   

update bedrock
join
(
  select pos_from, pos_to, 
  case when pos_to > pos_from then pos_from else pos_to end as pos_min,
  case when pos_to > pos_from then pos_to else pos_from end as pos_max,
  case when pos_to > pos_from then -1 else 1 end as pos_delta
  from (
    select 
    5 as pos_from, 
    3 as pos_to
  ) q1
) q2 on position between pos_min and pos_max
set position = case 
               when position = pos_from 
               then pos_to 
               else position + pos_delta 
               end;

select * from bedrock order by position;

id | position | name   
-: | -------: | :------
 3 |        1 | Betty  
 4 |        2 | Barney 
 1 |        3 | Fred   
 2 |        4 | Wilma  
 5 |        5 | Pebbles

db<>fiddle here

Upvotes: 1

Sandeep Sudhakaran
Sandeep Sudhakaran

Reputation: 1092

I hope, you are expecting this below query. 

select *,case when position = 3 then 5 when position = 5 then 3 else position end as new_position from customer order by new_position

Upvotes: -1

Related Questions