CODEWITHSUNDEEP
mysql
Vicky Thakor
Vicky Thakor

Reputation: 3916

Increment column value in mysql based on same ID

This is my table's current status.

id  operation  position
------------------------
1   EDIT          0
1   DELETE        0
2   VIEW          0
3   DELETE        0
3   VIEW          0
3   EDIT          0

I want to update position value in mysql say if for id = 1 1st entry set to 0 second occurrence for same id incremented by 1. So my final output should be like

id  operation  position
------------------------
1   EDIT          0
1   DELETE        1
2   VIEW          0
3   DELETE        0
3   VIEW          1
3   EDIT          2

How can i achive that, any hints ?

Upvotes: 1

Views: 615

Answers (1)

Harsh Gupta
Harsh Gupta

Reputation: 4538

set @prev_id = 0;
set @count = 0;

update actions inner join
(select @count := IF(@prev_id = id, @count + 1, 0) as count, @prev_id := id as prev_id, operation
from actions
order by id) as updated
on actions.id = updated.prev_id and actions.operation = updated.operation
set actions.position = updated.count

HTH

Edit: I named the table actions. Also, there was no column to uniquely identify a record, so I used combination of id and operation.

Upvotes: 2

Related Questions