Updating a new table column with the data in another table column randomly

Database
Enthusiast

Updating a new table column with the data in another table column randomly

Hi,

I have a Employee table with EmpName as a column in it,it contains 1000 records

I have another table named SubEmployee table with EmpName as a column in it,it contains 10 records

please help me in updating Employee table with SubEmployee table such that 10 records in SubEmployee table will be extrapolated into 1000 records in Employee in random fashion

7 REPLIES
Junior Contributor

Re: Updating a new table column with the data in another table column randomly

Untested:

update employee from
(select EmpName, row_number() over (order by EmpName) as rn
from subemployee) as s
set EmpName =s.EmpName
where random(1,10) = s.rn

Dieter
Enthusiast

Re: Updating a new table column with the data in another table column randomly

its updating but not in the random fashion,pls help me in updating in random fashion

Thanks in advance
Junior Contributor

Re: Updating a new table column with the data in another table column randomly

As long as the number of rows in the subemployee table is small you can easily use some dynamic SQL:

SELECT
CASE WHEN ROW_NUMBER() OVER (ORDER BY empname) = 1
THEN 'update employee set empname= case random(1,' || TRIM(COUNT(*) OVER ()) || ')'
ELSE ''
END ||
' when ' || TRIM(ROW_NUMBER() OVER (ORDER BY empname)) || ' then ''' || TRIM(empname) || '''' ||
CASE WHEN ROW_NUMBER() OVER (ORDER BY empname) = COUNT(*) OVER ()
THEN ' end'
ELSE ''
END
FROM subemployee

This is definitely the most efficient way.

Dieter
Enthusiast

Re: Updating a new table column with the data in another table column randomly

nice one Dieter
Is it possible to update entire row of employee table randomly from subemployee table.

Like this

update employee set name=(select name from subemployee order by random() where rownum=1)

pls help in correcting the above query so that update to the employee table will be made from select query output of subemployee table

Help me pls
Junior Contributor

Re: Updating a new table column with the data in another table column randomly

Why do you want to write a more complex query to achieve the same result?

It will look like this (untested):
UPDATE employee FROM
(SELECT * FROM
(SELECT EmpName, ROW_NUMBER() OVER (ORDER BY EmpName) AS rn FROM subemployee) AS s
JOIN ( SELECT customer_number, RANDOM(1,10) AS rnd FROM employee) AS e
ON rn=rnd) AS s
SET EmpName =s.EmpName
WHERE employee.customer_number = s.customer_number

Dieter
Enthusiast

Re: Updating a new table column with the data in another table column randomly

hi,

I don't have any common fields between two tables.

The update should be in the way that 10 records in the subemployee must be updated to employee table in random fashion ,in the query that u sent earlier only one value is updated to entier rows in the table

It means all the rows in the employee table is identical

help me in updating the employee table in random fashion,

pls avoid the use of cases
Junior Contributor

Re: Updating a new table column with the data in another table column randomly

"in the query that u sent earlier only one value is updated to entier rows in the table"

The first one? Maybe, didn't try.
The query using case? No.
The last one? Probably no, didn't try.

So #2 actually works.
Sorry, but i can't see any reason to write such a query.

Dieter