SQLServer實現修改表時將列賦值為隨機數

這裡沒有特殊要求或者限制。僅有的限制條件是：用純SQL來實現，而且我不想使用任何遊標，不想將表中記錄逐行遍曆，並隨機分配值。
背景

待解決問題的環境和表名等等可能不一樣，但是有相同的規律。
我從Vehicle表開始解決問題。該表和'Type'、'Colour'兩個表有對應關係。Colour表也包括串連到另一個表的'Finish'欄位（即粗糙或者光滑）。
我需要保證的是，所有指定類型的交通工具（比如小型轎車）的屬性，都是從colour表中根據finish欄位隨機分配的顏色。
問題的關鍵點：1.如何建立一個與指定標準相匹配的資料集合（包括多條記錄）；2.將上述資料集合指定到不同表（例如所有小型轎車）之內的記錄上。
我的解決方案是:

    產生一個暫存資料表，要求該表包含一條含有唯一數字，從資料集中擷取的隨機指定值的記錄。

    臨時指定一個唯一記錄到目標表的每一條記錄，用暫存資料表的唯一行實現表串連。

    用新串連的暫存資料表中隨機指定的值更新目標表。

我在文章中貼出了所有的必須的SQL指令碼，包括建立執行個體資料庫，填入一系列隨機資料，在有問題的地方，運行SQL指令碼即可。
產生資料庫中各表的指令碼

下列代碼是建立資料庫結構的SQL指令碼:

Vehicle 表
  
 

代碼如下	複製代碼
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 表
 

代碼如下	複製代碼
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 表
 

代碼如下

複製代碼

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 表
 
 

代碼如下	複製代碼
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

產生內容的指令碼

Vehicle 表的資料
 

代碼如下

複製代碼

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 表的資料
 
 

代碼如下	複製代碼
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 表的資料
 
 

代碼如下

複製代碼

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 表的資料
 
 
Insert Into Finish(Description) Values('Matte')
Insert Into Finish(Description) Values('Glossy')
用Colour表的主鍵隨機更新Vehicle表的指令碼
 
 

代碼如下

複製代碼

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

總結

但願我把問題描述清楚了。並且提供了一個能保持外鍵不變，且用隨機指定的值來更新資料庫表，的解決方案。
我不確定這類問題是否在CodeProject網站上發表過，但是我沒有找到任何一個涉及到該問題的文章。如果有擴充的需求，請通知我，我將全力以赴來實現您的需求。