John1984 John1984 - 3 months ago 8
MySQL Question

Incrementing a column for rows when record is updated earlier

Suppose I have a table structure like this:

ID (PK), Parent_ID (FK), Title, Position


And rows like these:

1, 1, Apple, 1
2, 1, Orange, 2
3, 1, Banana, 3
4, 1, Lime, 4
5, 1, Grapefruit, 5
6, 2, Lemon, 1


Now, let's say I want to run the following query:

UPDATE table SET Position = 1 WHERE ID = 3


How do I update the position field for other rows so that the numbers increment properly up to where the record originally was in the position list. Note that this should only effect rows with the same Parent_ID).

So when completed. it will look like this:

1, 1, Apple, 2
2, 1, Orange, 3
3, 1, Banana, 1
4, 1, Lime, 4
5, 1, Grapefruit, 5
6, 2, Lemon, 1


Thanks!

Answer

Update records where the position is between 1 and 3 (inclusive), but the ID does not equal with the id of the just updated record (3) and also provide the parent_id:

UPDATE table SET Position = Position + 1
WHERE ID <> 3 and Position>=1 and Position<3 and Parent_ID=1