I ran this query to populate a field with random numbers but it keeps populating with some duplicate records. Any idea how I can remove the duplicates?

UPDATE APRFIL
SET ALTATH = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);

Refer this link

https://web.archive.org/web/20090216200320/http://dotnet.org.za/calmyourself/archive/2007/04/13/sql-rand-trap-same-value-per-row.aspx

I agree RAND function generates same value for all the row. Since you are manipulating the data you should not get duplicates.

For example,

CREATE TABLE #Temp
(
   ItemDescription VARCHAR(30) NOT NULL
)

INSERT INTO #Temp (ItemDescription) VALUES ('abc')
INSERT INTO #Temp (ItemDescription) VALUES ('def')
INSERT INTO #Temp (ItemDescription) VALUES ('ghi')
INSERT INTO #Temp (ItemDescription) VALUES ('jkl')

SELECT RAND() FROM #Temp


SELECT CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000) FROM #Temp

UPDATE #Temp
SET ItemDescription = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);

select * from #Temp

DROP TABLE #Temp

--Prashanth

• Edited by Prashanth Jayaram 18 hours 48 minutes ago

Prasanth,

The table already exist. I only want to populate a column within the table with random non duplicate numbers.

Thanks

Prasanth,

The table already exist. I only want to populate a column within the table with random non duplicate numbers.

Thanks

RAND() function can generate duplicates in SET based approach.

You may try the below:(Please test it carefully)

UPDATE APRFIL
SET ALTATH = Abs(Checksum(NewId()))  

My complete test script as below:

create table APRFIL(ALTATH bigint)
Go
Insert into APRFIL Values(1)
Go 1000

Select * From APRFIL

UPDATE APRFIL
SET ALTATH = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)

;With cte as
(
Select *,ROW_NUMBER()over(partition by ALTATH Order by ALTATH asc) Rn From APRFIL
)Select* From cte Where rn>1

UPDATE APRFIL
SET ALTATH = Abs(Checksum(NewId()))  

;With cte as
(
Select *,ROW_NUMBER()over(partition by ALTATH Order by ALTATH asc) Rn From APRFIL
)Select* From cte Where rn>1

Select* From APRFIL

Drop table APRFIL

• Marked as answer by rotary Outsourcing 18 hours 25 minutes ago

Thank you very much.

That did it. No more duplicates....

Is there a query or a way to convert duplicates value in a column to non duplicates.

Hi Rotary,

Isn't Latheesh's solution what you want? The update statement indeed coverts the duplicates to non duplicates. Since the column holds random values, what sense does convert make here? Can you elaborate your requirement?

If you have any question, feel free to let me know.