Randomizing SQL data with data from table function

Question

I trying to make a sql script that will randomize the city, state, and zip code of a "members" table. I have made a table function that returns a single row with columns "city", "state" and "zip" taken from another database at random (via a view). This ensures that I get a city, state, and zip that actually correlate to each other in the real world.

From there I am trying to do something like this:

update t
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select
 City, 
 State,
 PostalCode from DATABASE.dbo.Member) t,
 DATABASE.dbo.getRandomCityState() citystate

Problem is, this only calls my function once, and puts the same city, state, and zip into every row of the table. Is there some way to call my function once for every row in the table?

Who on earth is blindly downvoting decent questions? (+1 to negate it.) — Mike Hofer, Dec 20 '11 at 20:28

score 1 · Answer 1 · answered Dec 20 '11 at 20:26

1

Use a CROSS APPLY

update t
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select
 City, 
 State,
 PostalCode from DATABASE.dbo.Member) t
 CROSS APPLY
 DATABASE.dbo.getRandomCityState() citystate

answered Dec 20 '11 at 20:26

Joe Stefanelli

132,803
19
237
235

Didn't work. I just ran that and it put the same values into every row. – mtmurdock Dec 20 '11 at 20:38

score 1 · Accepted Answer · answered Dec 20 '11 at 21:08

1

Ok so thanks to one of my co-workers, we found a solution. It would seem that since the function didn't take any parameters, SQL Server decided that the result would never change. So we tricked the server into thinking it will be different every time by passing a parameter to the function that was different: the id of each row. Since we were passing a different parameter each time, it called the function for every row.

update t 
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select top 10 
City, 
 State,
 PostalCode from TrajectoryServicesTest.dbo.Member) t
 cross apply SanitizePhi.dbo.getRandomCityState(t.MemberID) citystate

Kinda hacky, but it worked. Thanks to Joe for the help.

answered Dec 20 '11 at 21:08

mtmurdock

12,756
21
65
108

Look into Detirministic vs Non-Detirministic Functions. You want a non-detirministic function. In tsql you can 'force' a user defined function to be non-detirministic by including a function call to a non-detirministic function like CURRENT_TIMESTAMP or RAND. http://msdn.microsoft.com/en-us/library/ms178091.aspx – Ben English Dec 20 '11 at 23:13
Thats a great idea. Just a note, you cannot call RAND from a scalar-valued function. I've been dealing with it all day. The solution we ended up using was putting the RAND call into a view. – mtmurdock Dec 27 '11 at 16:35

Randomizing SQL data with data from table function

2 Answers2