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

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

Solution 2:

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;