1

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 ?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Vicky Thakor
  • 3,847
  • 7
  • 42
  • 67
  • possible duplicate of [How to get RowNumber() with Partition in MYSQL](http://stackoverflow.com/questions/19589707/how-to-get-rownumber-with-partition-in-mysql) – Alma Do Oct 30 '13 at 08:08
  • Remember, there's no row order in MySQL, so when inputting the data, there needs to be some method of establishing which row is 'first' already in place. – Strawberry Oct 30 '13 at 08:38

1 Answers1

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

Harsh Gupta
  • 4,348
  • 2
  • 25
  • 30
  • Er, this doesn't work (unless you delete that 'unique key'). In the absence of an ORDER BY clause, the PK's 'fall-back' position is to return operations in alphabetical order - which is not what the OP wanted – Strawberry Oct 30 '13 at 08:46
  • @Strawberry, Could you please elaborate your point? It really sounds confusing to me specifically at points `unless you delete that 'unique key'` and `In the absence of an ORDER BY clause`. – Harsh Gupta Oct 30 '13 at 08:49
  • If it's working fine then great - and, to be fair, in this particular scenario you probably don't have a lot of other options. For the future, you should probably include a timestamp column. – Strawberry Oct 30 '13 at 08:50
  • @Harsh. Test it yourself. Compare your result set with Vicky's. Here's a fiddle. http://www.sqlfiddle.com/#!2/8750e/1 – Strawberry Oct 30 '13 at 08:51
  • I posted after testing it (I didn't use sqlfiddle though). The `select` portion is just another way on how to return row number based on certain column, and then updated the table. I am just confused about `unless you delete that 'unique key'` portion of your comment. Also, I tend to answer based on an assumption that OP never post full table structure. So obviously, they are required to modify given answer to suit their actual objective. – Harsh Gupta Oct 30 '13 at 08:57
  • In MySQL, a table is not strictly a table unless it has a PRIMARY KEY. However, the act of constructing a composite PRIMARY KEY on (id,operation) will prevent your solution from working, as the rows will 'naturally' be returned in the 'wrong' order. – Strawberry Oct 30 '13 at 13:49
  • Okay. But as I pointed out in my answer and in previous comment, since there were no known primary keys in the given question. In order to explain the logic, I posted my answer that way, and OP is free to modify as per actual objective, which again I might add, be known to us or not. So the answer is correct in the context of question posted here. The statement `In MySQL, a table is not strictly a table unless it has a PRIMARY KEY` is not limited to `MySQL`. This is a mandatory constraint for any `RDBMS`. – Harsh Gupta Oct 30 '13 at 13:58