Multiple rows returned where identity column equals ?
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.
I think the "CAST(RAND(CAST(NEWID() AS VARBINARY))*3 + 1" part is getting evaulated against each row in the table. In affect I think the match is working like this: IF OBJECT_ID('tempdb..#Matching') IS NOT NULL DROP TABLE #Matching; SELECT ID , forename , CAST(RAND(CAST(NEWID() AS VARBINARY)) * 3 + 1 AS INT) AS IdToMatch INTO #Matching FROM @fnames; UPDATE people SET firstname = ( SELECT forename FROM #Matching WHERE id = IdToMatch); So sometimes you get one match, sometimes none, sometimes more than 1.