mysql procedure to update numeric reference in previous rows when one is updated

There's a table like this one

 ______________________
| id  |  title | order |
|----------------------|
|  1  |  test1 |   1   |
|-----|--------|-------|
|  2  |  test2 |   2   |
|-----|--------|-------|
|  3  |  test3 |   3   |
|-----|--------|-------|
|  4  |  test4 |   4   |
'----------------------'

when i introduce in my mysql shell a single update to a row

 $sql> UPDATE `table` SET order=1 WHERE id=3; 

And then procedure or method resamples order column in the before update lower va开发者_开发问答lues to get its order renewed like this

 ______________________
| id  |  title | order |
|----------------------|
|  1  |  test1 |   2   |
|-----|--------|-------|
|  2  |  test2 |   3   |
|-----|--------|-------|
|  3  |  test3 |   1   |
|-----|--------|-------|
|  4  |  test4 |   4   |
'----------------------'

Any help would be appreciated, thanks!

There are two cases to consider, I think:

1. Move one row so it appears earlier in the ordering.
2. Move one row so it appears later in the ordering.

It is non-trivial either way. It is not clear whether there is a unique constraint on the column 'order'; the end result is certainly supposed to have a unique ordering.

Notation:

• 'On' refers to the row with value 'order = n' in the old values
• 'Nn' refers to the row with 'order = n' in the new values

In the example (illustrative of case 1):

• O3 --> N1
• O1 --> N2
• O2 --> N3

As an alternative, consider moving id = 2 so it has order = 4:

• O2 --> N4
• O3 --> N2
• O4 --> N3

You are basically adding or subtracting one from the 'other' rows, where those are the rows in the old order between the old position of the moved row and the new position of the moved row. In a pseudo-code, using $old and $new to identify the before and after positions of the moved row, and dealing with case 1 ($old > $new):

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order >= $new AND order < $old THEN order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

The corresponding code for case 2 ($old < $new) is:

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order > $new AND order <= $old THEN order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

Given the WHERE clause on the UPDATE as a whole, you may be able to remove the second WHEN in the CASE and replace it with a simple ELSE.

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

I think a stored procedure is in order - choosing between the two statements based on the input parameters $old, $new. You might be able to do something with a judicious mix of expressions such as '($old - $new) / ABS($old - $new)' and 'MIN($old, $new)' and 'MAX($old, $new)' where the MIN/MAX are not aggregates but comparator functions for a pair of values (as found in Fortran, amongst other programming languages).

Note that I am assuming that while a single SQL statement is executing, the uniqueness constraint (if any) is not enforced as each row is changed - only when the statement completes. This is necessary since you can't actually control the order in which the rows are processed. I know of DBMS where this would cause trouble; I know of others where it would not.

It can all be done in a single SQL statement - but you do want a stored procedure to sort out the parameters to the statement. I use IBM Informix Dynamic Server (11.50.FC6 on MacOS X 10.6.2), and that is one of the DBMS that enforces the unique constraint on the 'order' column at the end of the statement. I did the development of the SQL without the UNIQUE constraint; that worked too, of course. (And yes, IDS does allow you to roll back DDL statements like CREATE TABLE and CREATE PROCEDURE. What did you say? Your DBMS doesn't? How quaint!)

BEGIN WORK;
CREATE TABLE AnonymousTable
(
    id      INTEGER NOT NULL PRIMARY KEY,
    title   VARCHAR(10) NOT NULL,
    order   INTEGER NOT NULL UNIQUE
);
INSERT INTO AnonymousTable VALUES(1, 'test1', 1);
INSERT INTO AnonymousTable VALUES(2, 'test2', 2);
INSERT INTO AnonymousTable VALUES(3, 'test3', 3);
INSERT INTO AnonymousTable VALUES(4, 'test4', 4);

SELECT * FROM AnonymousTable ORDER BY order;

CREATE PROCEDURE move_old_to_new(old INTEGER, new INTEGER)
    DEFINE v_min, v_max, v_gap, v_inc INTEGER;
    IF old = new OR old IS NULL OR new IS NULL THEN
        RETURN;
    END IF;
    LET v_min = old;
    IF new < old THEN
        LET v_min = new;
    END IF;
    LET v_max = old;
    IF new > old THEN
        LET v_max = new;
    END IF;
    LET v_gap = v_max - v_min + 1;
    LET v_inc = (old - new) / (v_max - v_min);
    UPDATE AnonymousTable
       SET order = v_min + MOD(order - v_min + v_inc + v_gap, v_gap)
     WHERE order BETWEEN v_min AND v_max;
END PROCEDURE;

EXECUTE PROCEDURE move_old_to_new(3,1);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(1,3);
SELECT * FROM AnonymousTable ORDER BY order;

INSERT INTO AnonymousTable VALUES(5, 'test5', 5);
INSERT INTO AnonymousTable VALUES(6, 'test6', 6);
INSERT INTO AnonymousTable VALUES(7, 'test7', 7);
INSERT INTO AnonymousTable VALUES(8, 'test8', 8);

EXECUTE PROCEDURE move_old_to_new(3,6);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(6,3);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(7,2);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(2,7);
SELECT * FROM AnonymousTable ORDER BY order;

ROLLBACK WORK;

The pairs of invocations of the stored procedure with the numbers reversed reinstated the original order each time. Clearly, I could redefine the v_inc variable so that instead of being just ±1, it was 'LET v_inc = v_inc - v_min + v_gap;' and then the MOD expression would be just 'MOD(order + v_inc, v_gap)'. I've not checked whether this works with negative numbers.

Adaptation to MySQL or other DBMS is left as an exercise for the reader.

A different approach is to use floating-point numbers instead of integers for the sorting. In this setup, you need to update only a single row when changing the sorting. Let's start with this:

id  order
 1     1
 2     2
 3     3
 4     4

Now you want to change the order so that item 2 appears between item 3 and 4. All you have to do is update item 2 so that its new order is a value between 3 and 4, for example 3.5:

id  order
 1     1
 2    3.5
 3     3
 4     4

I guess what you are trying to achieve is better done not with a DB query, but with a simple sorting construct.

You should save your db records as objects in a collection or structure/array/list of some kind, it depends on your language of choice.

Then you create a simple sorting algorithm, sort all records based on order modifying all orders of the other lines and create a procedure that updates all of the modified lines automatically after passing it the same collection.

Maybe do 2 update statements:

UPDATE `table` SET ord=ord+1 WHERE ord >= 1 order by ord desc;
UPDATE `table` SET ord=1 WHERE id=3;

(probably want to group those two operations into a single transaction instead of using autocommit)

EDIT: add "order by" to the first update to control the update order, avoiding the "duplicate key problem. (also, avoiding the "order" keyword in column names :-)

psuedo code:

CREATE TRIGGER reorder AFTER UPDATE ON `table` 
  FOR EACH ROW BEGIN
    UPDATE `table` SET order=order+1 WHERE id < 3 ORDER BY id DESC LIMIT 1; 
  END;
|

delimiter ;

Previous Id:

UPDATE `table` SET order=order+1 WHERE id < 3 ORDER BY id DESC LIMIT 1; 

Assuming you have passed in the id of the row to change as change_row_id and the new order as new_order:

current_order = SELECT order from 'table' WHERE id=change_row_id;
if (current_order > new_order)
  UPDATE `table` SET order=order+1 WHERE (order > new_order AND 
    order < current_order);
else
  [you'll have to figure out how you want to handle this.  Do you want the 
   orders to all be sequential with no breaks?]
ENDYIF;

UPDATE 'table' SET order=new_order WHERE id=change_row_id;

I don't know mysql, so you may need to tweak the sql. And you definitely want to do this in a single transaction. Either commit everything or nothing.

For all who has the same problem as the threadstarter but doesn't use a IDS as DBMS like @Jonathan Leffler: Here is a pastebin from these procedure for MySQL http://pastebin.com/AxkJQmAH