CODEWITHSUNDEEP
mysql
Oliver Williams
Oliver Williams

Reputation: 6354

MySQL re-order row priority field values and also make them sequential

Given the following table:

+--------+-------------------+-----------+
| ID     | Name              |  Priority |
+--------+-------------------+-----------+
| 1      | Andy              | 1         |
| 2      | Bob               | 2         |
| 3      | David             | 8         |
| 4      | Edward            | 9         |
| 5      | CHARLES           | 15        |
+--------+-------------------+-----------+

I would like to move CHARLES to between Bob and David by Priority value (ignore the alphabetical list, this is just to make the desired result obvious).

(Also note the Priority values may not be sequential)

To do this I need to change CHARLES' current Priority (15) to Bob's Priority+1, and update David and Edward's Priority to Priority+1.

I can DO this if I know two things, the id of CHARLES and the Priority value of the row he must be after (Bob):

UPDATE mytable SET Priority = 
IF(ID = :charles_id, :bob_priority + 1, 
IF(Priority >= :bob_priority, 
Priority + 1, Priority))

The PROBLEM or at least question is, how could I compress the resulting values to 1,2,3,4,5 instead of 1,2,3,9,10 - and do it in one shot?

Oracle has a "pseudo field" which is the index of the row, but I don't know of anything equivalent in MySQL.

Upvotes: 1

Views: 468

Answers (1)

Strawberry
Strawberry

Reputation: 33945

The first part of the problem is fairly trivial...

DROP TABLE IF EXISTS priorities;

CREATE TABLE priorities
(ID SERIAL PRIMARY KEY
,Name VARCHAR(12) NOT NULL
,Priority INT NOT NULL
,INDEX(priority)
);

INSERT INTO priorities VALUES
(101,'Andy',1),
(108,'Bob',2),
(113,'David',8),
(124,'Edward',9),
(155,'CHARLES',15);

UPDATE priorities a
  JOIN
     ( SELECT x.id,x.name, @i:=@i+1 priority FROM priorities x, (SELECT @i:=0) vars ORDER BY id) b
    ON b.id = a.id 
   SET a.priority = b.priority;

SELECT * FROM priorities
+-----+---------+----------+
| ID  | Name    | Priority |
+-----+---------+----------+
| 101 | Andy    |        1 |
| 108 | Bob     |        2 |
| 113 | David   |        3 |
| 124 | Edward  |        4 |
| 155 | CHARLES |        5 |
+-----+---------+----------+

Upvotes: 1

Related Questions