How can I update a field of a table with random numbers of specific range

Question

Scenerio:

The table is about number of registered students for specific exams. There are 4 columns:

1- ExamId.
2- studentId.
3- semesterId.
4- numeric code.(to keep students' identity hidden)

All fields except numeric code are filled already. After the all students are entered in the table. a numeric code will be RANDOMLY alloted to each student, the range of the numeric code vary on the numbr of students entered in the table. Like if there are 100 students the rang is 1-100.

The Requirments:

1. The numeric code must lye between 1 and maximum no of students entered in the table.

2. The numbers should be alloted randomly.

3. No number should be missed between the range.

4. No Duplicates are allowed.

I have tried many queries but all in vain.

I am working with Sql-Server-2008.

Thanks in anticipation.

Answer 1

Use ROW_NUMBER()OVER(ORDER BY NEWID() ASC). Something like this

UPDATE T SET numericcode = n
FROM ExamTable T
inner join
(SELECT ExamId,studentId,semesterId,ROW_NUMBER()OVER(ORDER BY NEWID() ASC) n     
FROM ExamTable)T2
    ON T.ExamId = T2.ExamID
    AND T.studentId = T2.studentId 
    AND T.semesterId = T2.semesterId 

Your Requirements:

1- The numeric code must be between 1 ~ maximum no of students entered in the table.

Since its ROW_NUMBER(), it will be between 1 AND max record in table.

2- The numbers should be allocated randomly.

ROW_NUMBER() is generated using NEWID() guarantees randomness.

3- No number should be missed between the range.

ROW_NUMBER() is incremental by 1 so no missed number

4- No Duplicates are allowed.

There are no duplicates when using ROW_NUMBER()