In my table named "stocktakings", there is a column from type ENUM called "status". "status" has the following fields:
I want to change the values to:
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?
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'
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With