1

How to update random row in database using rand() with where clause

Here is what I am trying right now, its not working not sure whats wrong

mysql_query("UPDATE `user` SET `token` = '$session' WHERE NOT toke = '1'  limit 1 rand()");
user3689285
  • 43
  • 1
  • 5
  • What’s wrong is that you just randomly added `RAND()` somewhere, without bothering about SQL syntax … – CBroe May 29 '14 at 21:52
  • possible duplicate of [Selecting Random Rows in MySQL](http://stackoverflow.com/questions/1283640/selecting-random-rows-in-mysql) – b.b3rn4rd May 30 '14 at 00:26

1 Answers1

4

Your query is not valid. First of all there is a typo in it, toke should be token. WHERE NOT should be WHERE token <> '1'. You can indeed use RAND(), but with on ORDER BY clause. So your query should look like this:

mysql_query("UPDATE `user` SET `token` = '$session' WHERE `token` <> '1' ORDER BY RAND() LIMIT 1");
Gergo Erdosi
  • 40,904
  • 21
  • 118
  • 94
  • I think she's trying to update random row, so the key point is UPDATE something WHERE uniqueID = rand(). If uniqueID is Primary Key then LIMIT isn't important, but very important is to range rand() to valid IDs or the query shouldn't update anything if rand() throws some number which not equals any Primary Key value. – Dusan May 29 '14 at 21:52
  • 2
    @Dusan `ORDER BY RAND()` creates a column with random values and sorts on it. Then `LIMIT 1` limits the number of rows to 1. So this query will update one random row. – Gergo Erdosi May 29 '14 at 22:00
  • note that `ORDER BY RAND()` has to create a random value for each row and then sorts accordingly. This can get pretty slow. I recommend picking a random number between 0 and the row count, then filter the query by offsetting via `LIMIT 1, random_number` – Felk May 29 '14 at 22:15