mysql - Making changes to multiple records based on change of single record with SQL

Question

I have a table of food items. They have a "Position" field that represents the order they should appear in on a list (listID is the list they are on, we don't want to re-order items on another list).

+--id--+--listID--+---name---+--position--+
|   1  |     1    | cheese   |      0     |
|   2  |     1    | chips    |      1     |
|   3  |     1    | bacon    |      2     |
|   4  |     1    | apples   |      3     |
|   5  |     1    | pears    |      4     |
|   6  |     1    | pie      |      5     |
|   7  |     2    | carrots  |      0     |
| 8,9+ |    3,4+  | ...      |     ...    |
+------+----------+----------+------------+

I want to be able to say "Move Pears to before Chips" which involves setting the position of Pears to position 1, and then incrementing all the positions inbetween by 1. so that my resulting Table look like this...

+--id--+--listID--+---name---+--position--+
|   1  |     1    | cheese   |      0     |
|   2  |     1    | chips    |      2     |
|   3  |     1    | bacon    |      3     |
|   4  |     1    | apples   |      4     |
|   5  |     1    | pears    |      1     |
|   6  |     1    | pie      |      5     |
|   7  |     2    | carrots  |      0     |
| 8,9+ |    3,4+  | ...      |     ...    |
+------+----------+----------+------------+

So that all I need to do is SELECT name FROM mytable WHERE listID = 1 ORDER BY position and I'll get all my food in the right order.

Is it possible to do this with a single query? Keep in mind that a record might be moving up or down in the list, and that the table contains records for multiple lists, so we need to isolate the listID.

My knowledge of SQL is pretty limited so right now the only way I know of to do this is to SELECT id, position FROM mytable WHERE listID = 1 AND position BETWEEN 1 AND 5 then I can use Javascript (node.js) to change position 5 to 1, and increment all others +1. Then UPDATE all the records I just changed.

It's just that anytime I try to read up on SQL stuff everyone keeps saying to avoid multiple queries and avoid doing syncronous coding and stuff like that.

Thanks

Answer 1

This calls for a complex query that updates many records. But a small change to your data can change things so that it can be achieved with a simple query that modifies just one record.

UPDATE my_table set position = position*10;

In the old days, the BASIC programming language on many systems had line numbers, it encouraged spagetti code. Instead of functions many people wrote GOTO line_number. Real trouble arose if you numbered the lines sequentially and had to add or delete a few lines. How did people get around it? By increment lines by 10! That's what we are doing here.

So you want pears to be the second item?

UPDATE my_table set position = 15 WHERE listId=1 AND name = 'Pears'

Worried that eventually gaps between the items will disappear after multiple reordering? No fear just do

UPDATE my_table set position = position*10;

From time to time.