how can i move column data to another table and set its id to foreign key column in first table?

Question

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

Answer 1

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;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=60661aae250012bcc4c9f72c1f6e2cb0

Now you can drop application.allowed_command column.