CODEWITHSUNDEEP
mysqlsql
John1984
John1984

Reputation: 987

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!

Upvotes: 0

Views: 38

Answers (2)

Shadow
Shadow

Reputation: 34231

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

Upvotes: 0

Nemeros
Nemeros

Reputation: 425

You do need to run several updates of the other rows.

A generic solution :

/** initial update or insert **/
UPDATE table SET Position = 1 WHERE ID = 3;

/** update of the other sequence **/
UPDATE table Set Position = CASE WHEN Position >= 1 THEN Position + 1 ELSE Position - 1 End where id <> 3

Upvotes: 1

Related Questions