I had table application that one of the columns names allowed_command , then I create another table application_command and want to separate allowed_command column and move to second table ‘application_command’ in addition, application table has foreign key from application_command so , I need to copy value of allowed_command to application_command table then put its id to first table
Separate new table creation makes no sense. I recommend you to drop it and create new instance during data copy.
Step 1. Create new table and copy data into it.
CREATE TABLE application_command ( application_command_id BIGINT AUTO_INCREMENT PRIMARY KEY, allowed_command VARCHAR(255) ) SELECT DISTINCT allowed_command FROM application;
Step 2. Create column for foreign key.
ALTER TABLE application ADD COLUMN application_command_id BIGINT, ADD FOREIGN KEY fk_allowed_command (application_command_id) REFERENCES application_command (application_command_id) ON UPDATE CASCADE ON DELETE SET NULL;
Step 3. Set the relation values.
UPDATE application JOIN application_command USING (allowed_command) SET application.application_command_id = application_command.application_command_id;
Now you can drop