title: MYSQL: How to update unique random number to existing rows
body: <p>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.</p>
  <p>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 <code>property_ref</code> which has default value as 1.</p>
  <pre><code>ALTER TABLE property ADD uniqueIdentifier INT DEFAULT (1) ;
  </code></pre>
  <p>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</p>
  <p>Here is the snippet I tried,</p>
  <pre><code>with cte as (
  select subIdentifier, id from (
      SELECT id, LPAD(FLOOR(RAND() * (999999 - 100000) + 100000), 6, 0) AS subIdentifier
      FROM property as p1
      WHERE &quot;subIdentifier&quot; 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 != ''
  </code></pre>
  <p>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.</p>
  <p>here is a current output
  [current result picture]</p>
  <p><img src="https://i.stack.imgur.com/TpnPF.png" alt="1" /></p>
  <p>If anyone can help, I am extremely thanks for that.</p>
  <p>Thanks</p>