从随机名称表更新SQl [英] SQl Update from table of random Names

问题描述

我有一个随机名称表，仅3列(id，firstname，lastname).

I Have a table of random names, simply 3 columns(id, firstname, lastname).

我正在尝试让SQL使用从该表中随机选择的名称来更新整个名称表.

I am trying to have SQL update an entire table of names with a random selected name from this table.

这是我正在使用的SQL.它似乎适用于某些记录，但并非总能完成所有记录，有时会留下NULL，或者并不总是更新所有行.有时它会影响9行，而有时它会影响11行...想法?

Here is the SQL i am using. It seems to work for some of the records, but it doesn't always do all of them, sometime leaving NULLS, or not always updating all rows. Sometime it runs affecting 9 rows, other times it says affected 11 rows... 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

推荐答案

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

这篇关于从随机名称表更新SQl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！