question

Adrian_1 avatar image
Adrian_1 asked

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.
Adrian
sql2012sql 2008r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
mjharper avatar image
mjharper answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cheers Matt, fascinating, it's hard to get my head round but it makes sense, of a sort :-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.