SQL command to update a field with a random but unique value from a list of values

Question

Consider the following table structure

table: Team

+-------+-------------+----------+
|   id  |  companyid  | teamcolor|
+-------+-------------+----------+
|   1   |      1      |    null  |
|   2   |      2      |    null  |  
|   3   |      2      |    null  |   
|   4   |      2      |    null  |  
|   5   |      3      |    null  | 
|   6   |      4      |    null  |
+-------+-------------+----------+

List of available colors:

SELECT * FROM (VALUES('#f44336'), ('#E91E63'), ('#E91E63'), ('#9C27B0'), ('#673AB7'), ('#3F51B5'), ('#2196F3'), ('#03A9F4'), ('#00BCD4'), ('#009688'), ('#4CAF50'), ('#8BC34A'), ('#CDDC39'), ('#FFEB3B')) N(Colour)

I need an SQL update to initialize the teamcolor field of the Team table with a random color from the list of colors. The color must also be unique by companyid.

Desired outcome

+-------+-------------+----------+
|   id  |  companyid  | teamcolor|
+-------+-------------+----------+
|   1   |      1      | '#f44336'|
|   2   |      2      | '#E91E63'|  
|   3   |      2      | '#03A9F4'|   
|   4   |      2      | '#8BC34A'|  
|   5   |      3      | '#f44336'| 
|   6   |      4      | '#FFEB3B'|
+-------+-------------+----------+

What I've done already

UPDATE T
SET TeamColour = C.Colour
FROM Team T
CROSS APPLY (
    SELECT TOP 1 Colour
    FROM (
        VALUES 
            ('#f44336')
            ,('#E91E63')
            ,('#E91E63')

            -- many rows
            -- ...
            -- many rows

            ,('#BF360C')
            ,('#3E2723')
            ,('#212121')
            ,('#263238')
        ) N(Colour)
    WHERE T.ID = T.ID
    ORDER BY NEWID()
    ) C

and

UPDATE T
SET TeamColour = C.Colour
FROM [Team] T
CROSS APPLY (
    SELECT TOP 1 *
    FROM [Team] T1
        ,(
            VALUES 
                ('#f44336')
                ,('#E91E63')
                ,('#E91E63')

                -- many rows
                -- ...
                -- many rows

                ,('#3E2723')
                ,('#212121')
                ,('#263238')
            ) N(Colour)
    WHERE NOT EXISTS (
            SELECT *
            FROM [Team] T2
            WHERE T2.Company_ID = T1.Company_ID
                AND T2.TeamColour = Colour
            )
        AND T.ID = T1.ID
    ORDER BY NEWID()
    ) C

But this does not create unique instances of colors by companyid as required.

Answer 1

You can use this.

DECLARE @Team TABLE (id INT, companyid INT, teamcolor VARCHAR(10))
INSERT INTO @Team VALUES
(1 , 1 , null ),
(2 , 2 , null ),  
(3 , 2 , null ),   
(4 , 2 , null ),  
(5 , 3 , null ), 
(6 , 4 , null )



;WITH CTE_Team AS
(
    SELECT *, 
       ROW_NUMBER() OVER(ORDER BY id) RNK 
    FROM @Team
)
, RdnColor AS (
    SELECT N.*
    , ROW_NUMBER() OVER( ORDER BY NEWID()) ID  
    , COUNT(*) OVER() CNT
    FROM (VALUES('#f44336'), ('#E91E63'), ('#E91E64'), ('#9C27B0'), ('#673AB7'), ('#3F51B5'), ('#2196F3'), ('#03A9F4'), ('#00BCD4'), ('#009688'), ('#4CAF50'), ('#8BC34A'), ('#CDDC39'), ('#FFEB3B')
    ) N(Colour)
)
UPDATE T
SET teamcolor = C.Colour
FROM CTE_Team T 
INNER JOIN RdnColor C ON (T.RNK % C.CNT) + 1  = C.ID

select * from @Team

Result:

id          companyid   teamcolor
----------- ----------- ----------
1           1           #9C27B0
2           2           #00BCD4
3           2           #FFEB3B
4           2           #673AB7
5           3           #03A9F4
6           4           #CDDC39