SQL Query to update random records with data

onmyway365 .

10y874SQL

Hi all,

I hope you can help me.

I am busy testing a C# app, and would like to update 200 database records with data for testing. I would like to update random 10% of certain fields with data.

This is the table structure:

SELECT [RowID]

,[Finance_Project_Number]

,[Finance_Project_Type]

,[Finance_Project_Description]

,[Finance_Project_Region]

,[Customer_Number]

,[Processing_Signal]

,[Processing_Result]

,[Processing_Result_Text]

,[Infor_Customer_Code]

,[Infor_Project_Number]

,[Insert_Datetime]

,[Processed_Datetime]

,[Infor_Row_ID]

,[Infor_Status]

,[Resolution_Message]

,[Resolution_Date]

FROM [InterfaceInfor].[dbo].[ProjectMaster]

I would like something like this (some pseudo code):

UPDATE TOP (200) * FROM [InterfaceInfor].[dbo].[ProjectMaster]

SET ([Finance_Project_Number] = '' (random 10%),

[Finance_Project_Type] = '' (random 10%), )

[Finance_Project_Region]= 'GAUTENG' (random 10%), )

[Processing_Result]= '0' (random 10%), )

[Finance_Project_Type] = '' (random 10%) )

Thank you in advance!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

onmyway365 .10y ago
Here is an example I was able to write for updating a single field. It doesn't update multiple, but it will work for now. It is only for testing purposes.

UPDATE [InterfaceInfor].[dbo].[ProjectMaster]
Set [Finance_Project_Region] = 'WESTERN CAPE'
where [RowID] IN
(
SELECT TOP 30 [RowID] FROM [InterfaceInfor].[dbo].[ProjectMaster]
WHERE [Processing_Result] = 0
ORDER BY NEWID()
)
Thanks for everyone's help!
+1
onmyway365 .10y ago
I want to update a random 10% of my 200 records for each example listed.

Example: Set [Finance_Project_Number] = '' for random 10% of TOP 200 record (i.e. 20 records)

Example2: Set [Finance_Project_Region] = 'GAUTENG' for random 10% of TOP 200 record (i.e. 20 records)

I Hope this makes sense!
0
Manas Mohapatra10y ago
You want to update 10% of a field or to increase 10% of a field? If you provide a demo example it would be better to write query.
0