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

@reds Make my table look like the second Example from my question when I want to move "Pears" before "Chips" — tyler mackenzie, Nov 16 '16 at 02:45
you want pears to be your second row record? is that what you mean? — Vijunav Vastivch, Nov 16 '16 at 02:50
@reds - Well "Pears" doesn't necessarily need to move records. because there could be any number of unrelated records in between. I just need the "Position" field to change, and the "Position" fields of any related records to increment accordingly. — tyler mackenzie, Nov 16 '16 at 03:06
@reds I've updated my Question a little, hopefully it's a little more clear what I'm asking now. — tyler mackenzie, Nov 17 '16 at 15:28

e4c5 · Answer 1 · 2016-11-16T12:43:17.970

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.

edited Nov 16 '16 at 12:43

answered Nov 16 '16 at 05:53

e4c5

52,766
11
101
134

This answer is clever, and almost answers my question. But what if I try to move something before Pears now? is there an easy way to set position = 12.5 or whatever? I feel like that starts to get just as complex. – tyler mackenzie Nov 16 '16 at 12:31
you don't need to go into decimals. You can set it as 12. Still needs only one query (with the aid of a join) to make the update. – e4c5 Nov 16 '16 at 12:41
would you be able to show me the query that would achieve this? Would it need to check if position 15 exists first? then 12, then 11, and how would I know when to set position * 10 again? – tyler mackenzie Nov 16 '16 at 18:17

score 1 · Answer 2 · answered Nov 19 '16 at 16:02

1

I do not think this can be conveniently done in less than two queries, which is OK, there should be as few queries as possible, but not at any cost. The two queries would be like (based on what you write yourself)

UPDATE mytable SET position = 1 WHERE listID = 1 AND name = 'pears';
UPDATE mytable SET position = position + 1 WHERE listID = 1 AND position BETWEEN 2 AND 4;

answered Nov 19 '16 at 16:02

comodoro

1,489
1
19
30

I like this. It's simpler then the solution that I came up with. Do you know if there is any pros/cons to building a single, more complicated SQL like mine over have 2 simpler queries like yours? – tyler mackenzie Nov 19 '16 at 16:18
1

The main reason I would prefer it is clarity, which means better maintainability for the future. I do not exactly see into MySQL innards, but I would suspect it might perform better than your solution with CASE, because standard operations like UPDATE are more focused on and therefore optimized. Which is probably moot if you do not have something like millions of records. – comodoro Nov 23 '16 at 15:24

score 0 · Answer 3 · answered Nov 19 '16 at 16:01

I've mostly figured out my problem. So I've decided to put an answer here incase anyone finds it helpful. I can make use of a CASE statement in SQL. Also by using Javascript beforehand to build my SQL query I can change multiple records.

This builds my SQL query:

var sql;
var incrementDirection = (startPos > endPos)? 1 : -1;
sql = "UPDATE mytable SET position = CASE WHEN position = "+startPos+" THEN "+endPos;
for(var i=endPos; i!=startPos; i+=incrementDirection){
    sql += " WHEN position = "+i+" THEN "+(i+incrementDirection);
}
sql += " ELSE position END WHERE listID = "+listID;

If I want to move Pears to before Chips. I can set:

startPos = 4;
endPos = 1;
listID = 1;

My code will produce an SQL statement that looks like:

UPDATE mytable
SET position = CASE
  WHEN position = 4 THEN 1
  WHEN position = 1 THEN 2
  WHEN position = 2 THEN 3
  WHEN position = 3 THEN 4
  ELSE position
END
WHERE listID = 1

I run that code and my final table will look like:

+--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+  | ...      |     ...    |
+------+----------+----------+------------+

After that, all I have to do is run SELECT name FROM mytable WHERE listID = 1 ORDER BY position and the output will be as follows::

cheese
pears
chips
bacon
apples
pie

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

3 Answers3

Linked

Related