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
Advertisement
Answer
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.
x
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.