Jump to content

Update Column with unique value


I-AM-OBODO

Recommended Posts

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";
}
	
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.
 

Link to comment
Share on other sites

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                     

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.