Updating SQl from random name table
I have a table of random names, all 3 columns ( id
, firstname
, lastname
).
I am trying to SQL update an entire name table with a randomly selected name from that table.
Here is the SQL I'm using. It seems to work for some records, but it doesn't always do all of them, leaves NULLS sometime, or doesn't always update all rows. Sometimes he works by touching 9 lines, other times he says he touched 11 lines ... thoughts?
update TestNames,rndnames
set TestNames.fname = rndnames.FirstName,TestNames.lname=rndnames.LastName
where rndnames.ID=floor(1+(rand()*600))
Answer:
update
TestNames left join
(select ID,
floor(1+(rand()*600)) as rndid
from TestNames) as TN on TN.ID=TestNames.id
left join rndnames on TN.rndid=rndnames.id
set TestNames.fname=rndnames.FirstName,TestNames.lname=rndnames.LastName
source to share
Here is the request:
update TestNames t cross join
rndnames r
set t.fname = r.FirstName,
t.lname = r.LastName
where r.ID = floor(1+(rand()*600));
It updates the row in testnames
when the random id chosen by the expression matches the id in the table. Are the values id
in rndnames
filled?
If your table is not very large and has id
, here's another approach:
update TestName t join
(select t.*,
(select id from rndnames order by rand() limit 1) as rndid
from testname t
) tr
on t.id = tr.id join
rndnames r
on t.rndid = r.id
set t.fname = r.FirstName,
t.lname = r.LastName;
EDIT:
I think this will work as well:
update TestNames t cross join
rndnames r
set t.fname = r.FirstName,
t.lname = r.LastName
where r.ID = (select id
from rndnames
order by rand()
limit 1
);
source to share