CODEWITHSUNDEEP
mysqlsqldatabase
Tmss G-ils
Tmss G-ils

Reputation: 153

adding unique values to existing table in mySql

Good Day

I created a table, NEW_TABLE, from some of another table columns ,OLD_TABLE.

I added a new column ID of type double

I want to fill the values of the ID column with unique values and then make it the the NEW_TABLE key.

Is there a way to do this in MySQL with a query or a set command?

I tried something like this:

Insert into NEW_TABLE
(select  generateId() , col1, col2 
 from ORIGINAL_TABLE)

Upvotes: 0

Views: 2011

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Why did you choose DOUBLE and not an integer datatype?

ALTER TABLE NEW_TABLE
  MODIFY ID INT UNSIGNED NOT NULL AUTO_INCREMENT ;

ALTER TABLE NEW_TABLE
  ADD CONSTRAINT new_table_pk
    PRIMARY KEY (ID) ;

and then:

INSERT INTO NEW_TABLE 
  (col1, col2)
SELECT col1, col2 
FROM ORIGINAL_TABLE ;

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24106

try this:

Insert into NEW_TABLE
(select   @row := @row + 1 as generateId, col1, col2 
 from ORIGINAL_TABLE, (SELECT @row := 0)row)

Upvotes: 1

Algorithmist
Algorithmist

Reputation: 6695

Usually you set the field to be an auto increment field when it is defined. To do so afterwards, you can use:

ALTER TABLE NEW_TABLE MODIFY ID int(10) unsigned NOT NULL auto_increment;

To then insert an new record and for it to automatically get an assigned ID, merely omit the field from the insert.

Upvotes: 2

mihaisimi
mihaisimi

Reputation: 2029

You should use autoincrement and an integer field (is there any reason for you to want a double key there?):

CREATE TABLE NEW_TABLE (
     id INT NOT NULL AUTO_INCREMENT,
     col1 CHAR(30) NOT NULL,
     col2 CHAR(30) NOT NULL,
     PRIMARY KEY (id)
)

Upvotes: 1

Related Questions