Unique random number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unique random number

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 03:50:53

Hi,

What I want to be able to do is to find all records in a table where data in field1 is the same and data in field2 is the same. I then want to generate a unique random number and update field3 with this number where the criteria above is true, but this generated number must not be the same as any other numbers stored within field3.

Can anyone help?

Cheers

Paul

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 03:53:29

Sorry guys, the criteria should read:

where data in field1 is the same and data in field2 is the same and data in field3 is the same.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-13 : 08:16:57

Hi Paul,

Something like this :


CREATE TABLE #TEST (UID INT, FIELD1 INT, FIELD2 INT, FIELD3 INT, FIELD4 INT)

INSERT INTO #TEST (UID, FIELD1, FIELD2, FIELD3, FIELD4)
SELECT	1, 1, 1, 2, NULL
UNION ALL
SELECT	2, 1, 2, 3, NULL
UNION ALL
SELECT	3, 2, 2, 2, 1
UNION ALL
SELECT	4, 5, 5, 5, NULL
UNION ALL
SELECT	5, 5, 5, 5, NULL

UPDATE #TEST
SET FIELD3 = (SELECT TOP 1  CONVERT(INT, RAND(CONVERT(BINARY(4),NEWID()))*100) 
		FROM	#TEST AS A
		WHERE A.UID = #TEST.UID
		AND CONVERT(INT, RAND(CONVERT(BINARY(4),NEWID()))*100) NOT IN (SELECT FIELD3 FROM #TEST))
WHERE	FIELD1 = FIELD2
AND	FIELD2 = FIELD3


DROP TABLE #TEST

The red can be changed to generate much larger number.

Actually, no need for the TOP 1 come to think about it

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 08:40:32

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources