Mysql procedure for updating a numeric link in previous lines when updating

Question

There's a table like this

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

when I add one update to my mysql shell to a line

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

And then the procedure or method reorders the order column to a lower value before updating to update its order as follows

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

Any help would be appreciated, thanks!

Answer 1

There are two cases to consider, I think:

• Move one row so it appears earlier in order.
• Move one row so it appears later in order.

This is not trivial anyway. It is unclear whether there is a unique restriction on the order of the columns; the end result, of course, must have a unique order.

Designations:

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

In the example (example 1):

• O3 → N1
• O1 → N2
• O2 → N3

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

• O2 → N4
• O3 → N2
• O4 → N3

You basically add or subtract one of the “other” lines, where these are the lines in the old order between the old position of the moved line and the new position of the moved line. In pseudocode, using $ old and $ new to determine the positions before and after the moved line and handle 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):

 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 in UPDATE as a whole, you can remove the second WHEN in 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 the stored procedure is fine - the choice between two statements based on the input parameters $ old, $ new. Perhaps you can do something with a reasonable combination of expressions like " ($old - $new) / ABS($old - $new) " and " MIN($old, $new) " and " MAX($old, $new) ", where MIN / MAX are not aggregates, but comparator functions for a pair of values ​​(as found in Fortran, among other programming languages).

Note that I assume that although one SQL statement is executed, the uniqueness constraint (if any) does not apply, since each row changes only when the statement completes. This is necessary since you cannot control the order in which the lines are processed. I know about DBMS, where it can cause problems; I know others wherever this is.

---

All of this can be done in a single SQL statement, but you want the stored procedure to sort the parameters in the statement. I use IBM Informix Dynamic Server (11.50.FC6 on MacOS X 10.6.2), and this is one of the DBMSs that applies a unique constraint in the "order" column at the end of the instruction. I did SQL development without UNIQUE restriction; it worked too. (And yes, IDS allows you to roll back DDL statements such as CREATE TABLE and CREATE PROCEDURE. What did you say? Does your DBMS not work? How weird!)

 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; 

A pair of callback stored procedure calls restores the original order each time. Clearly, I could override the v_inc variable v_inc that instead of being ± 1, it would be " LET v_inc = v_inc - v_min + v_gap; " and then the MOD expression would be just " MOD(order + v_inc, v_gap) " I did not check if this works with negative numbers.

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

Answer 2

Another approach is to use floating point numbers instead of integers for sorting. In this setting, you only need to update one row when changing the sort. 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 point 3 and 4. All you have to do is update element 2 so that its new order a value from 3 to 4, for example, 3.5:

 id order 1 1 2 3.5 3 3 4 4 

Answer 3

Maybe 2 update statements:

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

(maybe you want to group these two operations into one transaction instead of using autocommit)

EDIT: Add “order” to the first update to control the order of the update, avoiding the “duplicate key problem” (also avoiding the keyword “order” in the column names :-)

Answer 4

Assuming you went into 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 do not know mysql, so you may need to configure sql. And you definitely want to do this in one transaction. Either everything is either nothing or nothing.

Answer 5

I assume that what you are trying to achieve is best done not with a database query, but with a simple sort.

You must save your db records as objects in the form of a collection or structure / array / list, it depends on your choice.

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

Answer 6

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; 

Answer 7

For all those who have the same problem as threadstarter but do not use IDS as a DBMS, for example @Jonathan Leffler: Here is an example from this procedure for MySQL http://pastebin.com/AxkJQmAH