Assign random value in set to column when updating a table

Introduction

Having spent the last few hours trying to find a way of updating a table with a set of IDs from a linked table, but ensuring they are assigned randomly - I wanted to share my solution so that others can use it, or even better, improve upon it.

There were no specific requirements or limitations to this, other than I wanted to keep it all in SQL, and didn't want to use any cursors or iterate through table rows one by one, assigning a random value.

Background

The context of the problem and the names of tables etc., are all different but the principle is the same.

I started with a Vehicle table. This linked to additional 'Type' and 'Colour' tables along with a description. The Colour table also contained a 'Finish' (i.e., Matte or Glossy), which linked to a separate table.

I needed to make sure that all vehicles of a specific type (e.g., Small Car) were randomly assigned a colour from the Colour table with a specific finish (e.g., Glossy only).

The crux of the problem was how to create a set of values (including duplicates) that match a specific criteria (e.g., all glossy colours), and then assign those values at random to a set of records within a different table (e.g., all small cars).

My solution was:

1. Generate a temporary table containing a row with a unique row number, and a randomly assigned value from within a set of values.
2. Temporarily assign a unique row number against each record within the target table, joining that with the temporary table's unique row number.
3. Updating the target table with the randomly assigned value from the now joined temporary table.

I have included all of the SQL scripts necessary to build an example database, populate it with a set of arbitrary values, and run the SQL script that addresses the problem.

Scripts to generate database tables

The following code contains the SQL to create the database schema:

Vehicle Table

CREATE TABLE [dbo].[Vehicle](
    [VehicleID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [TypeID] [int] NOT NULL,
    [ColourID] [int] NULL,
 CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED 
(
    [VehicleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Type Table

CREATE TABLE [dbo].[Type](
    [TypeID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED 
(
    [TypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Colour Table

CREATE TABLE [dbo].[Colour](
    [ColourID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [FinishID] [int] NOT NULL,
 CONSTRAINT [PK_Colour] PRIMARY KEY CLUSTERED 
(
    [ColourID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Colour] ADD  CONSTRAINT [DF_Colour_FinishID]  DEFAULT ((1)) FOR [FinishID]
GO

Finish Table

CREATE TABLE [dbo].[Finish](
    [FinishID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Finish] PRIMARY KEY CLUSTERED 
(
    [FinishID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Scripts to generate content

Vehicle Table Data

Insert Into Vehicle(Description, TypeID) Values('Ford Focus',2)
Insert Into Vehicle(Description, TypeID) Values('Mini',1)
Insert Into Vehicle(Description, TypeID) Values('Ford Transit',4)
Insert Into Vehicle(Description, TypeID) Values('Audi A6',3)
Insert Into Vehicle(Description, TypeID) Values('VW Golf',2)
Insert Into Vehicle(Description, TypeID) Values('Robin Reliant',2)
Insert Into Vehicle(Description, TypeID) Values('Land Rover',3)
Insert Into Vehicle(Description, TypeID) Values('VW Polo',1)
Insert Into Vehicle(Description, TypeID) Values('VW Passat',3)
Insert Into Vehicle(Description, TypeID) Values('Vauxhall Corsa',2)
Insert Into Vehicle(Description, TypeID) Values('Ford Ka',1)
Insert Into Vehicle(Description, TypeID) Values('Smart Car',1)

Type Table Data

Insert Into Type(Description) Values('Small Car')
Insert Into Type(Description) Values('Medium Car')
Insert Into Type(Description) Values('Large Car')
Insert Into Type(Description) Values('Truck')

Colour Table Data

Insert Into Colour(Description, FinishID) Values('Red',1)
Insert Into Colour(Description, FinishID) Values('Blue',2)
Insert Into Colour(Description, FinishID) Values('Green',2)
Insert Into Colour(Description, FinishID) Values('Orange',1)
Insert Into Colour(Description, FinishID) Values('Yellow',2)
Insert Into Colour(Description, FinishID) Values('Silver',1)
Insert Into Colour(Description, FinishID) Values('Black',2)
Insert Into Colour(Description, FinishID) Values('White',2)
Insert Into Colour(Description, FinishID) Values('Purple',1)

Finish Table Data

Insert Into Finish(Description) Values('Matte')
Insert Into Finish(Description) Values('Glossy')

Script to update Vehicle table with random Colour IDs

Declare @TempTable Table(
RowNumber Int,
ColourID Int)

Declare @VehicleTypeID As Int = 1 --Set this to whatever finish type is required

Declare @FinishID Int = 1
Declare @Count Int = 1
Declare @NumberOfVehicles Int = (Select count(*)  from Vehicle where TypeID = @VehicleTypeID)


--This generates a temporary table with a unique row number and randomly assigned IDs
While (@Count <= @NumberOfVehicles)
Begin
Insert Into @TempTable values (@Count, (select top 1 ColourID 
      from Colour where FinishID = @FinishID order by checksum(newid())))
Set @Count = @Count + 1
End

--This updates the Vehicle table according to specific criteria 
--(Vehicle Type) and assigns the random IDs from the temporary table
Update
    TempVehicle 
Set 
    TempVehicle.ColourID = TempColour.ColourID 
From 
    (select 
        row_number() over(order by VehicleID) as RowNumber, 
        VehicleID, 
        TypeID, 
        ColourID 
    from 
        Vehicle 
    where 
        TypeID = @VehicleTypeID) as TempVehicle 
Right Join 
    @TempTable as TempColour on TempColour.RowNumber = TempVehicle.RowNumber
Where 
    TempVehicle.RowNumber = TempColour.RowNumber
And 
    TempVehicle.TypeID = @VehicleTypeID
And
    TempVehicle.ColourID Is Null
    --Included to ensure rows that have already been assigned
    --a random colour are not updated 

Summary

Hopefully this is clear enough for others to follow, and provides a solution to updating database tables with randomly assigned values that are not just random numbers, but maintain a link to foreign key IDs.

I'm not sure whether this has been addressed on CodeProject before, but I couldn't find an article that did cover the subject. If anything needs expanding upon, let me know and I will try my best.

History

• 23/08/2013 - First submitted.