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 ?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments