Randomly Assigning values to a column in table

Question

Post reply

Randomly Assigning values to a column in table

bdba

SSCommitted

Points: 1504
More actions
September 8, 2008 at 12:43 am

#197909

Hi,
I need to randomly assign values to a column in a table. I need to update a column in a table
with handful of values from another table.
Following is the scenario:
T1 is the Table to Update: I am just providing the relevant columns.
The values in TargetID column in table T1 is initially 0, I need to randomly assign them with the TargetID values from table T2
T1:
ID(PK int) TargetID(smallint)
111 0
222 0
333 0
444 0
T2 is the Table with values to be picked for assignment:It can have any number of values. I am just showing upto 4 for example
T2:
TargetID(smallint)
1
2
3
4
I want to update the table T1 so that it looks like this.The values from table T2 can be assigned to any ID values in table T1.
Note: I do not want to assign TargetID = 1 from T2 to any IDs in T1
Example resulting table T1 after update:
ID(PK int) TargetID(smallint)
111 2
222 3
333 4
444 3
I am currently trying to accomplish by selecting top n from the table T1 and ordering it by NewId(), but can't figure how to assign the values from T2.
Any thoughts would be helpful.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

On what basis you want to update targetid in T2 from T1

there shud be some criteria or condition 😉

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

bhuvnesh.dogra (9/8/2008)
On what basis you want to update targetid in T2 from T1
there shud be some criteria or condition 😉

Random - that's the point.

It looks very like a homework question, but could also be a component of a test data generator.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

[font="Courier New"]-- Set up some test data

CREATE TABLE ##T2 (TargetID smallint)

INSERT INTO ##T2

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8

CREATE TABLE ##T1 ([ID] INT, TargetID smallint)

INSERT INTO ##T1 ([ID])

SELECT 111 UNION ALL

SELECT 222 UNION ALL

SELECT 333 UNION ALL

SELECT 444 UNION ALL

SELECT 555 UNION ALL

SELECT 666 UNION ALL

SELECT 777

GO

-- Create a function to return the pseudo-random value of TargetID from ##T2

-- Note: CHANGE "ConnectRS" to the name of your database

CREATE FUNCTION [dbo].[fn_GetRandomT2Value]

()

RETURNS SMALLINT

AS

BEGIN

DECLARE @iRetVal smallint

SELECT @iRetVal = (SELECT TargetID FROM OPENROWSET('SQLOLEDB',

'Trusted_Connection=Yes; Server=(local);Database=ConnectRS',

'SELECT TOP 1 TargetID FROM ##T2 WHERE TargetID <> 1 ORDER BY NEWID()') )

RETURN @iRetVal

END

GO

-- Update ##T1 with random values of TargetID from ##T2

UPDATE ##T1 SET TargetID = [dbo].[fn_GetRandomT2Value]()

-- Check results

SELECT * FROM ##T1

-- Clean up

DROP TABLE ##T1

DROP TABLE ##T2

[/font]

Results:

ID TargetID

----------- --------

111 7

222 3

333 7

444 8

555 2

666 2

777 6

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bdba SSCommitted Points: 1504 More actions · Answer 4

Hi Chris

Thanks for you thoughts. However, without having to introduce a new function to do this,I have the following update script to do this. I am updating the ##T1 within a loop until i hit EOR

However, I am seeing same targetID for all the IDs in table ##T1 after updating. Any idea, what might be causing this.When Running the order by NewID() by iteself I am getting random TargetID results.

DECLARE @Rows int

SET @Rows = 0

WHILE EXISTS

(SELECT * FROM ##T1)

BEGIN

UPDATE ##T1

SET TargetID =

(SELECT TOP 1 TargetID

FROM ##T2 where TargetID not in (select TargetID from ##T2 where TargetID =1)

order by NewID())

SET @Rows = @Rows + @@rowcount

END

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

That's because you're expecting this section...

(SELECT TOP 1 TargetID

FROM ##T2 where TargetID not in (select TargetID from ##T2 where TargetID =1)

order by NewID())

...to run for each row in ##T1. But of course it only runs once per query. So ##T1.TargetID is updated with the same value for each row.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bdba SSCommitted Points: 1504 More actions · Answer 6

Hmm...i did the update without the looping through but still same values assigned to all Ids in ##T1.

I just tried yr method and it worked. I am trying to see a way without having to use a function.