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)
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.
try this:
Insert into NEW_TABLE
(select @row := @row + 1 as generateId, col1, col2
from ORIGINAL_TABLE, (SELECT @row := 0)row)
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)
)
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 ;
DOUBLEand notINTorBIGINT?