Update Column with unique value

[I-AM-OBODO]

Hi

I have a table that is filled with values already and now i want to add another column associate_id
How can i update the columns with a random number?
What i have is giving all the columns same number

thanks

 

	$associates_id =  mt_rand(10000, 99999);
	$stmt = $pdo->prepare("UPDATE associates SET associates_id= $associates_id ");
$stmt->execute();
$affected_rows = $stmt->rowCount();
	if ($affected_rows){
    
    echo "DONE";
    
}else{
    
    echo "NOT DONE";
}
	

[Barand]

"associates_id" should already be unique,and should remain thus.

Changing them to random values does not guarantee uniqueness.

And, yes, an update query without a WHERE clause will affect ALL rows.

[I-AM-OBODO]

5 minutes ago, Barand said:

"associates_id" should already be unique,and should remain thus.

Changing them to random values does not guarantee uniqueness.

And, yes, an update query without a WHERE clause will affect ALL rows.

Changing them to random values does not guarantee uniqueness.
How/what then will guarantee uniqueness?

"associates_id" should already be unique,and should remain thus
was omitted cos of an oversight before and thats why its being populated now

And, yes, an update query without a WHERE clause will affect ALL rows.
So in this case, how does one add the WHERE clause when you want all the columns to be affected without doing it one by one?

Thanks

[ginerjm]

So are you saying that your current table does NOT have a unique key in any other column? No primary (ie, unique) index at all? 

[I-AM-OBODO]

1 minute ago, ginerjm said:

So are you saying that your current table does NOT have a unique key in any other column? No primary (ie, unique) index at all? 

Nope. There's a primary key which is an id and its different to the associates_id.

[ginerjm]

I thought you wanted to put a random value in the new associates_id???  If you already have an associates_id, then why not use that value?

How many rows are we talking here?

Edited September 9, 2018 by ginerjm

[I-AM-OBODO]

3 minutes ago, ginerjm said:

I thought you wanted to put a random value in the new associates_id???  If you already have an associates_id, then why not use that value?

I have an id already but i want to add associate_id and populate the column with unique value without doing it one after the other.
The id column is different from the associate_id. The associate_id is the new column i want to add.

Edited September 9, 2018 by Mr-Chidi

[ginerjm]

So you are creating a new associate_id column but you don't want to put your current id value in it?   You are confusing me.

[ginerjm]

And once again - how many rows are in this table?

[ginerjm]

Ok - in the interest of progress, here is this:

Write a query to dump all of your current primary keys to an array

$q = 'select keycol from my_table';
$key_results = $pdo->query($q);
$key_values = $key_results->fetchall(PDO::FETCH_NUM);

Now write a prepared query something like this:

$q = 'update my_table set associate_id = :assoc_id
  where keycol = :key_value';
$qst = $pdo->prepare($q);

// Now write a loop to go thru the PK values and set up an array
// with the two needed parameters and execute the query to process them all

$parms = array();
foreach ($key_values as $k)
{
 $parms['keycol'] = $k;
 $parms['assoc_id'] = mt_rand(1000,99999);  // NOT A UNIQUE VALUE!!!!
 $qst->execute($parms);
}

 

 

Note:  Code above is un-tested.
 
This will update your table's 'associate_id' column with the values generated by the call(s) to mt_rand.  As noted here and by Barand, it will not be unique.  If you want something unique you'll have to solve that problem

 

Of course you have said you 'have an id' already.  Why are we not using that value instead of this new 'associate_id' column with a new almost-random value?

Edited September 9, 2018 by ginerjm

[I-AM-OBODO]

18 minutes ago, ginerjm said:

And once again - how many rows are in this table?

about 300 rows

[I-AM-OBODO]

7 minutes ago, ginerjm said:

Ok - in the interest of progress, here is this:

//Write a query to dump all of your current primary keys to an array

$q = 'select keycol from my_table';
$key_results = $pdo->query($q);
$key_values = $key_results->fetchall(PDO::FETCH_NUM);

 

// Now write a prepared query something like this:

 

Note:  Code above is un-tested.
 
This will update your table's 'associate_id' column with the values generated by the call(s) to mt_rand.  As noted here and by Barand, it will not be unique.  If you want something unique you'll have to solve that problem

Of course you have said you 'have an id' already.  Why are we not using that value instead of this new 'associate_id' column with a new almost-random value?

Thanks.
But it seems you don't understand what i want or trying to say. But i guess i will just have to start creating the table afresh.
The only issue i have now is that if mt_rand doesn't guarantee uniqueness, how can one create/generate a unique id then outside the primary key?

Thanks all the same

Edited September 9, 2018 by Mr-Chidi

[ginerjm]

What did I miss now?  I have updated your table with a set of random numbers in your new field.  As unique as mt_rand will do it

[I-AM-OBODO]

3 minutes ago, ginerjm said:

What did I miss now?  I have updated your table with a set of random numbers in your new field.  As unique as mt_rand will do it

Thanks

[ginerjm]

If you want to ensure that you have a unique value in the new associate_id column, why not just assign the value of a counter instead of mt_rand?  Easy change to my sample code.

[ginerjm]

Ps - I see that my posts are mangled up.  One does NOT have all of the code in it which is the one you quoted.  Please check the one that has all 3 code blocks in it.  My cut and paste doesn't seem to transfer into the forum correctly.

[I-AM-OBODO]

8 minutes ago, ginerjm said:

If you want to ensure that you have a unique value in the new associate_id column, why not just assign the value of a counter instead of mt_rand?  Easy change to my sample code.

Thanks. will try that out.

how do you mean assigning value of a counter?

[ginerjm]

Change the block of code that looks similar to be like this:

	$parms = array();
	$i = 0;    // initialize a counter
foreach ($key_values as $k)
{
 $parms['keycol'] = $k;
	$i++
 $parms['assoc_id'] = $i;
 $qst->execute($parms);
}
	

That will place the value of $i in the new field instead of a random number.  Of course you can initialize it to a larger value than 0 too.
 

Edited September 9, 2018 by ginerjm

[Barand]

If you want to guarantee uniqueness, first define the column as unique

ALTER TABLE associates
  ADD COLUMN `associates_id` INT NULL,
  ADD UNIQUE INDEX `associates_id` (`associates_id` ASC);

Then

UPDATE associates
    SET associates_id = 10000 + RAND()*900000; 

 if it unluckily fails due to a duplicate, try again                     

Edited September 9, 2018 by Barand
Spulling error

[ginerjm]

Sorry about my last post.  The forum really mangles things up as for formatting.

[ginerjm]

PS again.  I have a typo in my code please change the references to

$parms['keycol']

to be

$parms['key_value']

[Barand]

Oops! Too many zeros.

My previous post should have

RAND()*90000

and not

RAND()*900000

 

[I-AM-OBODO]

14 hours ago, Barand said:

Oops! Too many zeros.

My previous post should have

RAND()*90000

and not

RAND()*900000

 

Thanks. I have updated the column. But my question now is what will be best to generate a random number to avoid duplicate? or should something; if duplicate entry, regenerate a another code. something of that nature. just asking.

[Barand]

Did you read my earlier post?

 

[Barand]

I have just tried this code with a table of over 10,000 records, setting the unique id column to nulls first.

$k = 0;
do {
    $db->exec("UPDATE IGNORE test_random 
                SET id = 10000 + RAND()*90000
                WHERE id IS NULL");
    $res = $db->query("SELECT COUNT(*) 
                        FROM test_random
                        WHERE id IS NULL");
    $nulls = $res->fetchColumn();
    $k++;
    echo $nulls . ' duplicates<br>';
} while ($nulls);

echo "<br><br>That took $k iterations";

The output was

583 duplicates
67 duplicates
4 duplicates
0 duplicates


That took 4 iterations

EDIT: With only 300 records it took only the one iteration - all unique on first attempt.

Edited September 10, 2018 by Barand