Search code examples
mysqlalter

MySQL Alter table, add column with unique random value

I have a table that I added a column called phone - the table also has an id set as a primary key that auto_increments. How can I insert a random value into the phone column, that won't be duplicated. The following UPDATE statement did insert random values, but not all of them unique. Also, I'm not sold I cast the phone field correctly either, but ran into issues when trying to set it as a int(11) w/ the ALTER TABLE command (mainly, it ran correctly, but when adding a row with a new phone number, the inserted value was translated into a different number).

UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) + 1;

Table spec's

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| phone      | varchar(11)  | NO   |     | NULL    |                |
| age        | tinyint(3)   | NO   |     | NULL    |                |
| test       | tinyint(4)   | NO   |     | 0       |                |
| note       | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
Solution

  • Try this

    UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) * id;