CODEWITHSUNDEEP
mysqlsqllaravel
Malkios
Malkios

Reputation: 47

move decimals sql or divide

I built an administrative system in Laravel for my office, using mysql, one of the main tables is for the price of the products which is a double. The system has been in production for over 2 years now, but the government here changed our currency and removed 5 zeroes, so I need to change all the price values while keeping them with some decimals.

Currently, the lowest price in the list is 500 While the highest is 14985010

I need to perform a query to change ALL the price values in that column on the production DB (backup is done, so should be fine)

So the 500 one should be: 0.005 And the 14985010 should be: 149.8501

I'm thinking that all that is needed is to divide the values by 100000, but i might be wrong.

My SQL skills are super rusty, and I've been searching for a while but couldn't find the right answer. Any pointer would be greatly appreciated. Thanks!

Upvotes: 1

Views: 382

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You should first fix the column to be sure it can handle the appropriate decimal places. I would suggest something like:

alter table t modify column price decimal(38, 10);

Then just update the value:

update t
    price = price / 100000;

Having said that, you might want to check if the values are exactly what you expect:

select cast(cast(price as decimal(38, 10)) / 100000 as decimal(38, 10))

There may be subtle rounding errors that are not to your liking.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522386

I actually would recommend against just dividing all your currency data by some large factor, e.g. 100K. Instead, I propose adding a new column to your prices table, which is key into another table giving the factor which should be used to determine the current price. So you might have a setup like this:

prices table

price    | factorKey
500      | 2
14985010 | 2

factors table
factorKey | value
1         | 1
2         | 0.00001

Then, to generate the actual current prices, you could do a join:

SELECT
    p.price * f.value AS price
FROM prices p
INNER JOIN factors f
    ON p.factorKey = f.factorKey;

Under this scheme, you would only need to modify the current factor to be used with your price data. You could even maintain another table to keep track of the historical prices. One reason to suggest this is that perhaps your government will change prices again in the future. It is error prone to do such large manual divisions/multiplications on your original data.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562731

First of all, your currency data type should support decimal numbers, not just integers. I would suggest DECIMAL(12,4) for your column.

After you are sure the column data type can hold decimal numbers, you can update all rows in the table.

Here's a demonstration:

mysql> create table MyTable ( MyCurrencyColumn decimal(12,4));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into MyTable values (500), (14985010);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from MyTable;
+------------------+
| MyCurrencyColumn |
+------------------+
|         500.0000 |
|    14985010.0000 |
+------------------+
2 rows in set (0.00 sec)

mysql>  UPDATE MyTable SET MyCurrencyColumn = MyCurrencyColumn / 100000;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from MyTable;
+------------------+
| MyCurrencyColumn |
+------------------+
|           0.0050 |
|         149.8501 |
+------------------+

Upvotes: 0

Related Questions