CODEWITHSUNDEEP
mysqlsql
Vaibhav
Vaibhav

Reputation: 215

sql - Generate a unique random value for each row

I'm making an SQL table that stores data for students. For a column called roll_number, I want to generate a random number between two values, say 4000 and 5000 and assign it to the column for each row while making sure that the value has not been used before as roll number has to be unique.

Can anyone give me the query I can use for setting this up? I'm relatively new to SQL.

Upvotes: 0

Views: 1377

Answers (2)

vjy tiwari
vjy tiwari

Reputation: 861

You can create and check random number is already exist or not .

/// Get the unique roll_number
SELECT FLOOR(4000 + RAND() * 1000) AS roll_number
FROM student
WHERE roll_number NOT IN (SELECT roll_number FROM table)
LIMIT 1

UPDATE student set roll_number = ? WHERE student_id = ?

Upvotes: -1

Strawberry
Strawberry

Reputation: 33935

Select x.*, @i := @i + 1 randid from my_table, (Select @i:=4000) vars order by rand();

Upvotes: 2

Related Questions