I find this is fascinating and intriguing rather than frustrating and annoying... In attempting to anonymise some names in a table, I came up with the following: Please note I am not looking for another anonymisation routine, I want to know why this is going wrong! -- establish our table to anonymise CREATE TABLE people (firstname VARCHAR(20)) INSERT INTO people VALUES('Harry'),('Tom') -- provide a list of names to anonymise with DECLARE @fnames TABLE(id INT IDENTITY(1,1), forename VARCHAR(40)) INSERT into @fnames VALUES('jane'),('fred'),('john') -- randomise the names in people UPDATE people SET firstname = (SELECT forename FROM @fnames WHERE id = CAST(RAND(CAST(NEWID() AS VARBINARY))*3 + 1 AS INT)) select * from people -- Sometimes the update says "Subquery returned more then 1 value"(!) (surely not possible)
-- Sometimes it updates a firstname with null (also not possible)
-- Sometimes it works perfectly fine as expected
I thought I got it working by using a permanent table rather than a table variable, 'cos it worked fine the first time, but that was just a fluke and it behaves just as randomly (no pun intended) as the table variable.
Adrian
