i'm trying anonymize data in database, i'm renaming people in it. asked similar question earlier, , told use newid force creation of new value per updated row, in situation doesn't seem working.
what doing wrong?
-- create table customer create table #firstname ( id int, firstname nvarchar(255) null, gender nvarchar(255) null ) create table #lastname ( id int, lastname nvarchar(255) ) -- bulk insert import data text or csv file bulk insert #firstname 'c:\users\jhollon\desktop\tmp\names\firstnames.lined.txt' ( firstrow = 1, fieldterminator = ',', rowterminator = '\n' ) bulk insert #lastname 'c:\users\jhollon\desktop\tmp\names\lastnames.lined.txt' ( firstrow = 1, fieldterminator = ',', rowterminator = '\n' ) /*select firstname #firstname id = ( select randomnumber ( select abs(checksum(newid())) % 1500 randomnumber tbltenant sex = '1' ) );*/ update tbltenant set tenantname = ( select lastname + ', ' + firstname (select upper(firstname) firstname #firstname id = (select abs(checksum(newid())) % 500 + 1501)) a, (select lastname #lastname id = (select abs(checksum(newid())) % 200 + 1)) b ) sex = '2'; update tbltenant set tenantname = ( select lastname + ', ' + firstname (select upper(firstname) firstname #firstname id = (select abs(checksum(newid())) % 500 + 1)) a, (select lastname #lastname id = (select abs(checksum(newid())) % 200 + 1)) b ) sex = '1'; drop table #firstname; drop table #lastname;
correct. subquery evaluated once advertised ("cachable scalar subquery")
try uses newid derived table
update t set tenantname = l.lastname + ', ' + f.firstname tbltenant t cross apply (select top 1 upper(firstname) firstname #firstname checksum(newid()) <> t.id order newid()) f cross apply (select top 1 lastname #lastname checksum(newid()) <> t.id order newid()) l 
Comments
Post a Comment