CODEWITHSUNDEEP
mysql
Jason
Jason

Reputation: 15335

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

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?

Upvotes: 0

Views: 80

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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

Upvotes: 0

Andreas Storvik Strauman
Andreas Storvik Strauman

Reputation: 1655

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'");
}
?>

Upvotes: 1

Related Questions