clientinfo table
------------------------------------------------------------ ||clientinfoid | clientid | fname | mname | lname || ||1 | 1 | don | | smith|| ||2 | 1 | dan | | smith|| ||3 | 1 | dan | g | smith|| ||4 | 2 | john | d | doe || ------------------------------------------------------------ trying sql call right in sql server. i've written user defined function generates random first/middle/last names working fine. challenge want records same clientid updated result of single call rename function (actually 3 calls = 1 first, middle, , last name).
my attempt below chaning every record in clientinfo different names instead of giving clientid = 1 records same f/m/last names, clientid = 2 same f/m/last names, etc.
desired result:
------------------------------------------------------------ ||clientinfoid | clientid | fname | mname | lname || ||1 | 1 | bill | x | brown || ||2 | 1 | bill | x | brown || ||3 | 1 | bill | x | brown || ||4 | 2 | kate | q | ramirez || ------------------------------------------------------------ actual result:
---------------------------------------------------------------- || clientinfoid |clientid | fname | mname | lname || || 1 | 1 | bill | x | brown || || 2 | 1 | sue | r | henderson|| || 3 | 1 | phil | s | anders || || 4 | 2 | kate | q | ramirez || ---------------------------------------------------------------- my sql call
update clientinfo set firstname = x.newfirstname ,middlename = x.newmiddlename ,lastname = x.newlastname ( select uniqueclientid ,lastclientinfoid ,newfirstname ,newmiddlename ,newlastname ( ( select clientid uniqueclientid ,max(clientinfoid) lastclientinfoid clientinfo group clientid ) inner join ( select clientinfoid ,newfirstname = dbo.fnsamplefnamemnamelname(0, @maxname, '') ,newmiddlename = dbo.fnsamplefnamemnamelname(1, @maxname, middlename) ,newlastname = dbo.fnsamplefnamemnamelname(2, @maxname, '') clientinfo ) b on a.lastclientinfoid = b.clientinfoid ) ) x clientid = x.uniqueclientid
solved it. moved creation of names attached each clientid temp table first. joined on clientinfo on temp table pull in new sample names.
select clientid, newfirstname, newmiddlename, newlastname #tempsamplenames ( ( select clientid, max(clientinfoid) maxclientinfoid clientinfo group clientid ) inner join ( select clientinfoid ,newfirstname = dbo.fnsamplefnamemnamelname(0, @maxname, '') ,newmiddlename = dbo.fnsamplefnamemnamelname(1, @maxname, middlename) ,newlastname = dbo.fnsamplefnamemnamelname(2, @maxname, '') clientinfo ) b on a.maxclientinfoid = b.clientinfoid ) update clientinfo set firstname = b.newfirstname ,middlename = b.newmiddlename ,lastname = b.newlastname clientinfo inner join #tempsamplenames b on a.clientid = b.clientid drop table #tempsamplenames 
Comments
Post a Comment