Updating MySQL with shuffled numbers between 1 and 50 I need to assign a number to each userId.  I think this is too much of a query for updating.  Is it possible to reduce the number of queries, or is it okay?

def newRanking(groupId: String) = {
    DB.withConnection { implicit con =&gt;
      var rank: List[Int] = util.Random.shuffle(List(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50))
      var i = 0
      val sql = SQL("SELECT userId FROM `groupInformation` WHERE groupId={groupId} limit 50").on("groupId" -&gt; groupId)
      sql().map { row =&gt;
        SQL("update groupInformation set rank={rank} where groupId={groupId} and userId={userId}").on("groupId" -&gt; groupId, "rank" -&gt; rank(i), "username" -&gt; row[String]("userId")).executeUpdate()
        i += 1
      }.toList
    }
  }
As I'm sure you've noticed, executing any SQL query in a loop is a bad idea.  It would be much better to formulate a statement that accomplishes the entire batch of changes at once.

To achieve that goal, you'll need to combine several ideas:


Shuffling using ORDER BY RAND().  Admittedly, the documentation admits:


  RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version."


So, you'll have to decide whether MySQL's idea of randomness is as good as Scala's.
A row number generator.  Unfortunately, MySQL has no row_number() or rank() function, like some other databases, so you'll need to use a hack.
UPDATE using multiple table references.


A statement that you could use is:

UPDATE `groupInformation`
    INNER JOIN (
            SELECT *
                FROM `groupInformation`
                ORDER BY RAND() LIMIT 50
        ) AS shuffled
            ON `groupInformation`.userId = shuffled.userId
    INNER JOIN (
            SELECT @row := 0
        ) AS row_var_decl
    SET `groupInformation`.rank = @row := @row + 1
    WHERE `groupInformation`.groupId = {groupId};


Here's an SQL Fiddle as a demonstration.



As you've noted, the behaviour of the query above is different from the original when there are fewer than 50 matching rows in groupInformation.