MYSQL Change Enum Values

Question

In my table named "stocktakings", there is a column from type ENUM called "status". "status" has the following fields:

'Pending'
'Processing'
'Failed'
'Succeeded'

I want to change the values to:

'ready for relocation'
'search shelf location'
'stock update succeeded'
'stock update failed'
'updating stock'

I tried the following command:

ALTER TABLE `stocktakings` MODIFY `stocktakings.status` 
    `stocktakings.status` ENUM(
        `ready_for_relocation`,
        `search_shelf_location`,
        `stock_update_succeeded`,
        `stock_update_failed`,
        `updating_stock`
);

Didn't work. Any ideas?

Possible duplicate of [Laravel Migration - Update Enum Options](https://stackoverflow.com/questions/25251186/laravel-migration-update-enum-options) — Egretos, Oct 31 '18 at 07:31
You are going from 4 enum values to 5 enum values. How do they map ? — Madhur Bhaiya, Oct 31 '18 at 07:34

Madhur Bhaiya · Accepted Answer · 2018-10-31T08:24:13.597

You cannot just remove the Old Enum values from the Column definition. Otherwise, there will be an irreparable Data loss (all fields may turn to blank). It will be a multi-step process:

Add new Enum Values to Existing Column definition. Also, Enum values are surrounded by single quotes (not backticks)

ALTER TABLE `stocktakings` MODIFY `stocktakings`.`status` ENUM(
        'Pending', 
        'Processing', 
        'Failed', 
        'Succeeded', 
        'ready_for_relocation',
        'search_shelf_location',
        'stock_update_succeeded',
        'stock_update_failed',
        'updating_stock'
);

Now, run an Update query to Update all the enum values correspondingly:

UPDATE `stocktakings` 
SET status = CASE status 
               WHEN 'Pending' THEN 'ready_for_relocation'
               WHEN 'Processing' THEN 'search_shelf_location'
               WHEN 'Failed' THEN 'stock_update_failed'
               WHEN 'Succeeded' THEN 'stock_update_succeeded'
             END

Now, Alter Table and remove the Old enum values.

ALTER TABLE `stocktakings` MODIFY `stocktakings`.`status` ENUM(
            'ready_for_relocation',
            'search_shelf_location',
            'stock_update_succeeded',
            'stock_update_failed',
            'updating_stock'
    );

MYSQL Change Enum Values

1 Answers1