Update a field with an incrementing value that resets based on field

Question

I have the following table in MySQL:

|id|user|line|
|10|0001|    |
|11|0001|    |
|12|0002|    |
|13|0003|    |
|14|0003|    |
|15|0003|    |

I need to update the line column to be an incrementing number that resets for each user so I woudl end up with this:

|id|user|line|
|10|0001|   1|
|11|0001|   2|
|12|0002|   1|
|13|0003|   1|
|14|0003|   2|
|15|0003|   3|

I've used ROW_NUMBER() OVER (PARTITION... for this in MS SQL in the past. Is there a way to do this in MySQL?

@AndreasStorvikStrauman - Sure...in MySQL is preferred but PHP works. — Jason, Apr 25 '14 at 15:09
Okay. If you could elaborate on why you want to achieve this, we may be able to find an alternate solution. I'll write a PHP solution answer for you :) — Andreas Storvik Strauman, Apr 25 '14 at 15:10

score 1 · Answer 1 · answered Apr 25 '14 at 15:24

I don't know how you would do this with pure SQL. You could look at MySQLs GROUP BY function and see if that gets you anywhere?

Here is a PHP solution:

<?php
$mysql_host="localhost";
$mysql_user="MYSQL USERNAME";
$mysql_password="MYSQL PASSWORD";
$mysql_database_name="SOME_DB_NAME";
$table="MYSQL TABLE NAME";

$con=mysql_connect($mysql_host, $mysql_user, $mysql_password);
mysql_selectdb($mysql_database_name, $con);
$query=mysql_query("SELECT * FROM `$table`");

// $toUpdate will be used to store the info that is needed to update
// the appropriate line with the respective line number

// $userTracker will contain which "line" number we are on
while(mysql_fetch_assoc($query) as $row){
    $rowID=$row['id'];
    $userNumber=$row['user'];
    $nextLineNumberForThisUser=$userTracker[$userNumber];
    if (!$nextLineNumberForThisUser){
        $nextLineNumberForThisUser=1;
    }
    else{
        ++$nextLineNumberForThisUser;
    }

    $u=&$toUpdate[];
    $u['id']=$rowID;
    $u['line']=$nextLineNumberForThisUser;
    $userTracker[$userNumber]=$nextLineNumberForThisUser;
}


foreach ($toUpdate as $row){
    $id=$row['id'];
    $line=$row['line'];
    mysql_query("UPDATE `$table` SET `line`='$line' WHERE `id`='$id'");
}
?>

Looks like this works but I'm going to go with @Ravinder's answer since it's a SQL solution. The PHP solution is a little slow considering I have > 5000 records to deal with. — Jason, Apr 25 '14 at 16:47
Of course you have to go with the answer that answers your question! :) — Andreas Storvik Strauman, Apr 25 '14 at 23:01

score 0 · Accepted Answer · answered Apr 25 '14 at 15:42

MySQL Solution:

update 
  table_name t,
  ( select 
        id, user, line
      , case when @prev=(@curr:=user)
             then @row_num:=(@row_num + 1)
             else @row_num:=1
        end as unique_user_row_num
      , @prev:=@curr as temp_curr
    from table_name,
         (select @prev:='', @curr:='', @row_num:=0) row_nums
  ) d
set 
  t.line = d.unique_user_row_num
where 
  t.id=d.id
;

Demo @ MySQL 5.5.32 Fiddle

Update a field with an incrementing value that resets based on field

2 Answers2

Linked