April 7, 2009 at 8:53 am
Hi,
I am trying to update values in a column based on a value randomly selected from the same column but only end up with all the same values. I'm sure this is fairly simple but I can't get my head round it this afternoon!!!
I have tried:
[font="System"]UPDATE anonymiser
SET title = (SELECT TOP 1 title
FROM anonymiser
ORDER BY NewID()
)[/font]
I am trying to get one random value from the title column and update the current value. Sample values I am using are:
Mr
Ms
Master
Dr
Mrs
Miss
So for each record in the anonymiser table change the title for one of the values above.
Hopefully someone will be able to help me with this.
Thanks in advance.
John
April 7, 2009 at 9:08 am
Since you are not referring to any outer table columns in your inner table, the optimizer smartly generates an execution plan that reads the inner table only once. That's why you get only one value in your update.
To make to completely random, you have to force the optimizer to generate a inner table row for each outer table row by referring the outer table column in the inner query.
UPDATE anonymiser
SET title = ( SELECT TOP 1 title FROM anonymiser t WHERE anonymiser.title != '' ORDER BY NewID() )
--Ramesh
April 7, 2009 at 9:12 am
Suggest that you view this School Video by Randy Warren
http://www.sqlservercentral.com/articles/Video/65076/. It appears to fit perfectly as a answer to your problem
April 7, 2009 at 9:17 am
Outstanding, thank you very much for your help Ramesh that's done the trick!!
Thank you also for your reply bitbucket, I use the newid() to get a different title already which is a useful feature.
Thanks again.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply