CODEWITHSUNDEEP
mysqlrandomsql-update
Rod
Rod

Reputation: 13

MYSQL Update random selection of X records

I have a table of students (called students), this table has a char field called SchoolLevel indicating what level they are in (H -> "high", M -> "middle" E -> "elementary"). I would like to randomly assign students in each level to respective schools with an int field called 'schoolID'. So for example, if I have 4530 elementary students

schoolID=1, should be assigned to 3000 random 'E' students

schoolID=2, should be assigned to another 530 random 'E' students

and schoolID=3 should be assigned to the remaining unassigned 1000 records ('E' students).

I tried to do something like this:

update students
set SchoolID = 1 where SchoolLevel=(Select SchoolLevel from students where SchoolLevel='E' order by RAND() limit 300)

That clearly does not work - apparently you cannot specify the target table as the same table for update in the FROM clause. Can I even do what I am trying to do, i.e., update a random selection of X records?

Upvotes: 1

Views: 927

Answers (1)

peterm
peterm

Reputation: 92805

Try it this way (assuming that SchoolID initially IS NULL)

UPDATE students
   SET SchoolID = 1 
 WHERE SchoolLevel = 'E'
   AND SchoolID IS NULL
 ORDER BY RAND()
 LIMIT 3000;

Then

UPDATE students
   SET SchoolID = 2 
 WHERE SchoolLevel = 'E'
   AND SchoolID IS NULL
 ORDER BY RAND()
 LIMIT 530;

and so on

Here is SQLFiddle example

Upvotes: 1

Related Questions