MySQL - How do I update the decimal column to allow more digits?

Question

I'm a beginner in MySQL, and I accidentally created a table with a column named

(price decimal(2,2));

It needs to be decimal(4,2) to allow 4 digits. Since I've already created it, what is the easiest way to update that decimal value to decimal(4,2)? Or do I need to drop that column completely, and re-create it with the correct numbers?

Answer 1

ALTER TABLE mytable MODIFY COLUMN mycolumn newtype

example:

ALTER TABLE YourTableNameHere MODIFY COLUMN YourColumnNameHere decimal(4,2)

Answer 2

Just ALTER TABLE with the MODIFY command:

ALTER TABLE `table` MODIFY `price` DECIMAL(4,2)

This would allow for 2 decimals and 2 full numbers (up to 99.99). If you want 4 full numbers, use 6,2 instead (which would allow up to 9999.99).

Answer 3

It's not a matter of 'UPDATE' it's a matter of changing your table's structure. For that, use ALTER TABLE with MODIFY clause:

ALTER TABLE YourTableName MODIFY COLUMN price DECIMAL(4,2);

sqlfiddle demo

Answer 4

use CHANGE

ALTER TABLE table_name CHANGE OLD_COLUMN_NAME OLD_COLUMN_NAME datatype;

an example

ALTER TABLE table_name CHANGE price price decimal(4,2);

Answer 5

You can change it dynamically:-

ALTER TABLE ".$row["table_name"]." MODIFY ".$row1["Field"]." decimal(".$rdataType[0].",".$increaseValue.")

For more info:

Repository