MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE application (
    application_id BIGINT,
    allowed_command VARCHAR(255)
);

INSERT INTO application VALUES 
(1,'Move Left'), (1,'Move Right'), 
(2,'Move Left'), (2,'Jump Up'), (2,'Jump Down');

SELECT * FROM application;

Records: 5  Duplicates: 0  Warnings: 0




application_id
allowed_command


1
Move Left

1
Move Right

2
Move Left

2
Jump Up

2
Jump Down


-- 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;

Records: 4  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Rows matched: 5  Changed: 5  Warnings: 0


SELECT * FROM application;
SELECT * FROM application_command;


application_id
allowed_command
application_command_id


1
Move Left
1

1
Move Right
2

2
Move Left
1

2
Jump Up
3

2
Jump Down
4




application_command_id
allowed_command


1
Move Left

2
Move Right

3
Jump Up

4
Jump Down


SELECT * 
FROM application
JOIN application_command USING (application_command_id);


application_command_id
application_id
allowed_command
allowed_command


1
1
Move Left
Move Left

2
1
Move Right
Move Right

1
2
Move Left
Move Left

3
2
Jump Up
Jump Up

4
2
Jump Down
Jump Down

application_command_id	application_id	allowed_command	allowed_command
1	1	Move Left	Move Left
2	1	Move Right	Move Right
1	2	Move Left	Move Left
3	2	Jump Up	Jump Up
4	2	Jump Down	Jump Down