Random number from table to be updated-CodePudding

I have a table with EmployeeID, EmployeeName, EmployeeDeptID. I am trying to enter some sample data for this table. I received text file with EmployeeID and EmployeeName and I loaded it to Employee table.

I was told that values for EmployeeDeptID is 2010,2590,2877 and null. I am asked to assign randomly EmployeeDeptID to Employees. I am not sure how to do it

This is what I tried.

Create Table dbo.Employee(EmployeeID INT,EmployeeName VARCHAR(100), EmployeeDeptID INT)

I used 'import data' to load to dbo.Employee.

I created another table

Create table temp.SampleEmployeeDeptID(EmployeeDeptID int)
insert into temp.SampleEmployeeDeptID(EmployeeDeptID)values(null)
insert into temp.SampleEmployeeDeptID(EmployeeDeptID)values(2010)
insert into temp.SampleEmployeeDeptID(EmployeeDeptID)values(2590)
insert into temp.SampleEmployeeDeptID(EmployeeDeptID)values(2877)

I know I have to use this SELECT TOP 1 * FROM temp.SampleEmployeeDeptID ORDER BY newid() to get a random EmployeeID but do not know how to use it in Update statement.

Any help is appreciated. Is there any other easier method?

CodePudding user response：

Use "ORDER BY Rand()" to pull a random department ID.

Update dbo.Employee
Set EmployeeDeptID = (Select TOP 1 * from temp.SampleEmployeeDeptID Order By 
Rand()) 
Where EmployeeID = '<employee id here>';

Probably want to use a cursor to iterate through all rows in dbo.Employee

CodePudding user response：

If you want to use your temp table, you can use it as explained in this post.

Alternatively you can use the RAND() function, as explained here