2

I need to update a field called FamName in a table called Episode with randomly generated Germanic names from a different table called Surnames which has a single column called Surname.

To do this I have first added an ID field and NONCLUSTERED INDEX to my Surnames table

ALTER TABLE Surnames 
ADD ID INT NOT NULL IDENTITY(1, 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX idx ON Surnames(ID);
GO

I then attempt to update my Episode table via 

UPDATE E  
SET E.FamName = S.Surnames 
FROM Episode AS E 
INNER LOOP JOIN Surnames AS S 
    ON S.ID = (1 + ABS(CRYPT_GEN_RANDOM(8) % (SELECT COUNT(*) FROM Surnames)));
GO

where I am attempting to force the query to 'loop' using the LOOP join hint. Of course if I don't force the optimizer to loop (using LOOP) I will get the same German name for all rows. However, this query is strangely returning zero rows affected.

Why is this returning zero affected rows and how can this be amended to work?

Note, I could use a WHILE loop to perform this update, but I want a succinct way of doing this and to find out what I am doing wrong in this particular case.

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
MoonKnight
  • 23,214
  • 40
  • 145
  • 277
  • 2
    Why are you using `LOOP` join,let the query optimizer decided whether it wants to do a LOOP , MERGE or HASH join – Yosi Dahari Nov 29 '13 at 12:46
  • When you run this query: `SELECT * FROM Episode AS E INNER LOOP JOIN Surnames AS S ON S.ID = (1 + ABS(CRYPT_GEN_RANDOM(8) % (SELECT COUNT(*) FROM Surnames)));` how many rows returned? My guess is 0. that means you probably have something wrong with your `JOIN` criteria (i.e the `ON` part) – Yosi Dahari Nov 29 '13 at 12:49
  • @Yosi I explained _why_ in the question. Without the loop all rows get returned, _again_ this was stated in the question... – MoonKnight Nov 29 '13 at 13:01

1 Answers1

3

You cannot (reliably) affect query results with join hints. They are performance hints, not semantic hints. You try to rely on undefined behavior.

Moving the random number computation out of the join condition into one of the join sources prevents the expression to be treated as a constant:

UPDATE E  
SET E.FamName = S.Surnames 
FROM (
 SELECT *, (1 + ABS(CRYPT_GEN_RANDOM(8) % (SELECT COUNT(*) FROM Surnames))) AS SurnameID
 FROM Episode AS E 
) E
INNER LOOP JOIN Surnames AS S ON S.ID = E.SurnameID

The derived table E adds the computed SurnameID as a new column.

You don't need join hints any longer. I just tested that this works in my specific test case although I'm not whether this is guaranteed to work.

usr
  • 168,620
  • 35
  • 240
  • 369
  • I added it. Almost nothing else changed. My answers are sometimes a little short... – usr Nov 29 '13 at 13:08
  • I released after I asked. Sorry to waste your time... Thanks very much, nice simple approach. I also learned something about join hints so thanks. – MoonKnight Nov 29 '13 at 13:09