SQLServer assign a column to a random number when modifying a table

There are no special requirements or restrictions. The only restriction is that it is implemented using pure SQL, and I do not want to use any cursors, do not want to traverse records in the table row by row, and randomly allocate values.
Background

The environment and table name to be resolved may be different, but they have the same rule.
I started to solve the problem from the Vehicle table. The table corresponds to the 'type' and 'colorur' tables. The Colour table also contains the 'save' field (rough or smooth) connected to the other table ).
What I need to ensure is that the attributes of all specified types of vehicles (such as small cars) are the colors randomly allocated from the color table based on the finish field.
Key points: 1. how to create a data set that matches the specified criteria (including multiple records); 2. specify the above data set to the records in different tables (such as all small cars.
My solution is:

Generate a temporary table that contains a record containing a unique number that is randomly specified from the dataset.

Temporarily specify each record of a unique record to the target table, and use the unique row of the temporary table to connect the table.

Use the value specified randomly in the newly connected temporary table to update the new target table.

I posted all the necessary SQL scripts in the article, including creating an instance database and entering a series of random data. If there is a problem, run the SQL script.
Generate scripts for each table in the database

The following code is an SQL script used to create a database structure:

Vehicle table
  
 

The code is as follows:	Copy code
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
 

The code is as follows:	Copy code
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
 

The code is as follows:

Copy code

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
 
 

The code is as follows:	Copy code
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

Script for generating content

Vehicle table data
 

The code is as follows:

Copy code

Insert Into Vehicle (Description, TypeID) Values ('Ford fos', 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
 
 

The code is as follows:	Copy code
Insert Into Type (Description) Values ('small car ') Insert Into Type (Description) Values ('medium cart ') Insert Into Type (Description) Values ('large car ') Insert Into Type (Description) Values ('truck ')

Data in The Colour table
 
 

The code is as follows:

Copy code

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 ')
Update the script of the Vehicle table randomly with the primary key of the Colour table
 
 

The code is as follows:

Copy code

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 -- Removed ded to ensure rows that have already been assigned -- A random color are not updated

Summary

I hope I can clearly describe the problem. In addition, it provides a solution that can keep the foreign key unchanged and update the database table with a random value.
I'm not sure if I have posted this issue on the CodeProject website, but I have not found any article about it. If you want to scale your application, please let me know and I will do my best to meet your requirements.