CODEWITHSUNDEEP
mysql
Vadim Nosyrev
Vadim Nosyrev

Reputation: 113

Update the decimal column to allow more digits after decimal point

I have the next column, decimal(20,2) in MySQL tables:

no  
10.01
10.09
10.10
10.11
10.19
10.99

What is the easiest way to update that decimal value to:

no

10.001
10.009
10.010
10.011
10.099
..
10.100
10.101

If I changed column to decimal(20,3), I get the next numbers: 10.010,10.090 ... 10.990 etc. Each number must be unique. If MySQL can't do it, how to do it using php?

Upvotes: 2

Views: 386

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can use a query like this. first set your field to decimal(20,3): do it in 2 steps: first add also 1 to prevent duplicate entries. second subtract 1

first step

UPDATE youtTable
set val = 
  CAST(val as unsigned integer) +1 
  + (val - CAST(val as unsigned integer)) / 10;

second step

UPDATE youtTable
set val = val -1;

but you can also do it with one query if you change the lowest number first:

UPDATE yourTable
set val = 
  CAST(val as unsigned integer) 
  + (val - CAST(val as unsigned integer)) / 10
ORDER by val ASC;

sample

mysql> SELECT  CAST(10.19 as unsigned integer) + (10.19 - CAST(10.19 as unsigned integer)) / 10;
+----------------------------------------------------------------------------------+
| CAST(10.19 as unsigned integer) + (10.19 - CAST(10.19 as unsigned integer)) / 10 |
+----------------------------------------------------------------------------------+
|                                                                        10.019000 |
+----------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

Upvotes: 2

Vinoth Raj
Vinoth Raj

Reputation: 296

 *****Using MySql*****

 --> SELECT FORMAT('COLUMN NAME',DECIMAL VALUE);

  *****FOR EXAMPLE:*****

 --> SELECT FORMAT(NO,3);


         *****QUERY*****

      Create table '#test1'
      (no decimal(5,3));

 insert into '#test1' values
 ('10.01'),
 ('10.09'),
 ('10.10'),
 ('10.11'),
 ('10.19'),
 ('10.99');

        ****RESULT****
           [![DEMO IMAGE][1]][1]

     [1]: https://i.sstatic.net/AzZpB.jpg

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44854

I would do this as below

  • First take the back up of the table.
  • Then add a new column with decimal(20,3)
  • Then update the column with the value from old column by doing some string operation.
  • Drop the old column
  • Rename the new column.

Here is a test case

mysql> create table test (no decimal(20,2));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into test values('10.01'),('10.09'),('10.10'),('10.11'),('10.19'),('10.99');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-------+
| no    |
+-------+
| 10.01 |
| 10.09 |
| 10.10 |
| 10.11 |
| 10.19 |
| 10.99 |
+-------+
6 rows in set (0.00 sec)

mysql> alter table test add column no_new decimal(20,3);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-------+--------+
| no    | no_new |
+-------+--------+
| 10.01 |   NULL |
| 10.09 |   NULL |
| 10.10 |   NULL |
| 10.11 |   NULL |
| 10.19 |   NULL |
| 10.99 |   NULL |
+-------+--------+
6 rows in set (0.00 sec)

Then 

mysql> update test set no_new = concat(substring_index(no,'.',1),'.',concat('0',substring_index(no,'.',-1)));
Query OK, 6 rows affected (0.05 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from test;
+-------+--------+
| no    | no_new |
+-------+--------+
| 10.01 | 10.001 |
| 10.09 | 10.009 |
| 10.10 | 10.010 |
| 10.11 | 10.011 |
| 10.19 | 10.019 |
| 10.99 | 10.099 |
+-------+--------+
6 rows in set (0.00 sec)

mysql> alter table test drop column no;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test change no_new no decimal(20,3);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+--------+
| no     |
+--------+
| 10.001 |
| 10.009 |
| 10.010 |
| 10.011 |
| 10.019 |
| 10.099 |
+--------+
6 rows in set (0.00 sec)

Upvotes: 0

Related Questions