Sql-server – Update all rows from a table with random foreign key from another table

sql servertableupdate

I have four tables: (show on image below)

  • Topic whit columns:
    • ID
      • primary key
      • int
    • CategoryID
      • references to table Category
      • int
    • UserID
      • references to table User
      • nvarchar(128)
  • User with column:
    • ID
      • primary key
      • nvarchar(128)
  • Category whit column
    • ID
      • primary key
      • int
  • UserCategoryLink with columns
    • UserID
      • primary key
      • references to table User
      • nvarchar(128)
    • CategoryID
      • primary key
      • references to table Category
      • int

I will to update table Topic so that the CategoryID is one of the CategoryID's of the table UserCategoryLink.

P.S.: I use MS SQL Server.

3th update: Like you can see on this SQL Fiddle, the category from the topics form every user is not included into the table UserCategoryLink but it must! So can Marc only have topic whit category cycling and snowboarding and not from moto and swimming. Now I will to update all the wrong (correct can also be updated) rows to an ID from the column CategoryID from the table UserCategoryLink.

1st update: I have try the following code

;WITH rs AS 
(
    SELECT UserID as Sid, CategoryID as Cid
    FROM UserCategorieLink
)
update Topic 
    set CategoryID = (select top 1 Cid from rs where UserID = Sid) 
    where UserID = (select top 1 Sid from rs where UserID = Sid);

But the result is not random chosen by the favorite categories for every user. I know it comes from top 1 but it is because I don't know how to generate random numbers.

I will take a random row from a select statement (see code below / like I can do in a programming language like C#) but it gives me an error because RAND generates a random number between 0 and 1.

RAND(select top 1 Cid from rs where UserID = Sid)

The errors are:

Incorrect syntax near the keyword 'select'

and

Incorrect syntax near ')'.

on the 7th line.

2nd update: The code give me also the same random number for each topic that comes from the same user. Can that also be a random number? I know that every user has always two favorite categories. The query must pick one of them.

I know that it comes from the where clause of the update statement. But how can I fix it?

Best Answer

Not going to go through what you're trying to accomplish, but for your update, you're using a TOP without an ORDER BY. While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either. If you want to force a random row to be returned, you'll need to order by a row-level random number. For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) ).

Try it out against sys.objects to see the behavior of explicitly ordering randomly.

SELECT  TOP 1 name
FROM    sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );

And good luck with whatever it is you're doing.

Edit:

@jean points out in the comments just NEWID() is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() ) since I tend to need row-level randomization with a % operator.

Edit 2:

So something like this, then?

UPDATE  t
    SET CategoryID = nt.CategoryID
FROM    dbo.Topic t
INNER JOIN (    SELECT  t.ID, ucl.CategoryID, 
                        Ordinal = ROW_NUMBER() OVER (
                            PARTITION BY t.ID
                            ORDER BY NEWID() )
                FROM    dbo.Topic t
                INNER JOIN dbo.UserCategoryLink ucl
                    ON  t.UserID = ucl.UserID ) nt
    ON  t.ID = nt.ID
WHERE   nt.Ordinal = 1;
Related Question