Assign random value in set to column when updating a table





0/5 (0 vote)
A method to update a table column with a random set of specified values
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:
- Generate a temporary table containing a row with a unique row number, and a randomly assigned value from within a set of values.
- Temporarily assign a unique row number against each record within the target table, joining that with the temporary table's unique row number.
- Updating the target table with the randomly assigned value from the now joined temporary table.
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.