Random number from table to be updated

Question

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?

score 1 · Accepted Answer · answered May 10 '22 at 21:33

Here's one possible solution you can try, I've mocked up your sample table:

I generate a list of random ID values and a surrogate key which can then be joined to the existing table, again with a surrogate Id, which can then be updated using a CTE:

/* Sample data*/
Create Table dbo.Employee(EmployeeID INT,EmployeeName VARCHAR(100), EmployeeDeptID INT);
insert into Employee select 1,'Person 1', null;
insert into Employee select 2,'Person 2', null;
insert into Employee select 3,'Person 3', null;
insert into Employee select 4,'Person 4', null;


with v as (
    select v, Row_Number() over(order by NewId()) Id
    from (values(2010),(2590),(2877),(null))x(v)
), e as (
    select *, Row_Number() over (order by EmployeeId) Id
    from Employee
), u as (
    select e.EmployeeDeptID, v.v 
    from v join e on e.Id = v.Id
)
update u set employeeDeptId = v;

/* Test result */
select * from Employee;

See working Demo FIddle

Lucretius · Answer 2 · 2022-05-10T21:42:02.470

Use ORDER BY Rand() to pull a random EmployeeDeptID from temp.SampleEmployeeDeptID, then use a cursor to iterate through each individual row of dbo.Employee to set the new value.

DECLARE @EmployeeID as INT;
DECLARE @EmployeeCursor as CURSOR;

SET @EmployeeCursor = CURSOR FOR
SELECT EmployeeID From dbo.Employee

OPEN @EmployeeCursor;
   FETCH NEXT FROM @EmployeeCursor INTO @EmployeeID;
   WHILE @@FETCH_STATUS = 0
      BEGIN
         SET Employee.EmployeeDeptID = (Select TOP 1 * from temp.SampleEmployeeDeptID Order By Rand()) Where EmployeeID = @EmployeeID
END

Random number from table to be updated

2 Answers2