MYSQL: How to update unique random number to existing rows

Question

It's been my first question to this website, I'm sorry if I used any wrong keywords. I have been with one problem from quite a few days.

The Problem is, I have a MYSQL table named property where I wanted to add a ref number which will be a unique 6 digit non incremental number so I alter the table to add a new column named property_ref which has default value as 1.

ALTER TABLE property ADD uniqueIdentifier INT DEFAULT (1) ;

Then I write a script to first generate a number then checking it to db if exist or not and If not exist then update the row with the random number

Here is the snippet I tried,

with cte as (
select subIdentifier, id from (
    SELECT id, LPAD(FLOOR(RAND() * (999999 - 100000) + 100000), 6, 0) AS subIdentifier
    FROM property as p1
    WHERE "subIdentifier" NOT IN (SELECT uniqueIdentifier FROM property as p2) 
) as innerTable group by subIdentifier
)
UPDATE property SET uniqueIdentifier =  (
  select subIdentifier from cte as c where c.id = property.id 
) where property.id != ''

this query returns a set of record for almost all the rows but I have a table of entries of total 20000, but this query fills up for ~19000 and rest of the rows are null.

here is a current output [current result picture]

If anyone can help, I am extremely thanks for that.

Thanks

Answer 1

Instead of trying to randomly generate unique numbers that do not exist in the table, I would try the approach of randomly generating numbers using the ID column as a seed; as long as the ID number is unique, the new number will be unique as well. This is not technically fully "random" but it may be sufficient for your needs.

https://www.db-fiddle.com/f/iqMPDK8AmdvAoTbon1Yn6J/1

update Property set 
UniqueIdentifier =   round(rand(id)*1000000)
where UniqueIdentifier is null

SELECT id,  round(rand(id)*1000000) as UniqueIdentifier FROM test;