CODEWITHSUNDEEP
phpmysqlsql
user3605847
user3605847

Reputation: 65

Adding ending zero's to a mysql table column

I have a MySQL table named "Amount", which has amounts like 200, 300, 100 etc. But, what I want is, that the amounts should be 200.00, 300.00,100.00. How do I write a query to update the column, so that it adds the ending zeroes? The column is float.

I tried with a regular update query, but it failed and I had to revert the changes (re-upload a backup sql file).

Any suggestions are welcome.

Upvotes: 0

Views: 1029

Answers (3)

cs45977
cs45977

Reputation: 400

MySql Docs

I think your best solution is an update your column

something like ALTER TABLE Amount MODIFY amount float(7,2) NOT NULL DEFAULT 0;

Upvotes: 2

mrdeveloper
mrdeveloper

Reputation: 538

update Amount set floati = concat(floati,'.00');

Example: http://sqlfiddle.com/#!2/fa240/1

Upvotes: 0

Rudie
Rudie

Reputation: 53781

Number columns in MySQL (and probably most db engines) don't actually save numbers like that, but binary. If you're interested, it's probably something like IEEE.

There is a data type that does save numbers literally: DECIMAL. If you save 5.2 into a DECIMAL(2, 4), it saves the number as 5.2000. Decimals are saved as strings and only 'converted' into a number when it's saved into the db.

You could try altering the column into a DECIMAL(n, 2), but depending on how smart MySQL is, that doesn't update the values correctly:

ALTER TABLE your_table
  MODIFY your_column DECIMAL(5, 2) NOT NULL DEFAULT 0 -- etc

Upvotes: 0

Related Questions