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


To update the decimal column to allow more digit, use the MODIFY COLUMN. The syntax is as follows:

ALTER TABLE MODIFY COLUMN yourColumnName DECIMAL(yourIntValue,yourIntValue);

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table allowDecimalWithMoreDigit
   -> (  
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Salary DECIMAL(3,2),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.64 sec)

Now you can check the description of table using DESC command. The syntax is as follows:

DESC yourTableName;

Now you can check the description of table using above command. The query is as follows:

mysql> desc allowDecimalWithMoreDigit;

The following is the output:

+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| Id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| Salary | decimal(3,2) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
2 rows in set (0.18 sec)

Look at the sample output, the column Salary has DECIMAL(3,2). Now you can change the DECIMAL(10,4) using MODIFY COLUMN with ALTER command. The query is as follows:

mysql> alter table allowDecimalWithMoreDigit modify column Salary DECIMAL(10,4);
Query OK, 0 rows affected (2.46 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now check the table description once again. The query is as follows:

mysql> desc allowDecimalWithMoreDigit;

The following is the output:

+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| Id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| Salary | decimal(10,4) | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Look at the sample output, Salary data type DECIMAL(3,2) has been changed to DECIMAL(10,4).

Updated on: 30-Jul-2019

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements