Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Number format in mysql update query

Posted on 2005-05-05
Medium Priority
Last Modified: 2008-02-01
Hi Everybody

I got the following update query in Mysql:

UPDATE wijnen SET Prijs_inc = Prijs*1.19;

The query works but I get numbers like 1.456778
I would like them to be 1.46

Is there a way to format the numers using the above query of some other query?

Thanx in advance.
Question by:Geerd
  • 3
LVL 32

Assisted Solution

Batalf earned 500 total points
ID: 13934816
Maybe you should set the type of field Prijs_inc to decimal(8,2) or something like that!?


alter table wijnen modify  Prijs_inc decimal(8,2);

An alternative is to use format() when you selects data from the query


select format(Prijs_inc,2) from wijnen
LVL 32

Accepted Solution

Batalf earned 500 total points
ID: 13934831
Or maybe you could simply use

UPDATE wijnen SET Prijs_inc = format(Prijs*1.19,2);
LVL 32

Expert Comment

ID: 13935163
Thanks for the points.

Please remember that the format() function formats a number like 12345.3454 like this

12,345.34 if you use format(Prijs*1.19,2);

i.e. with a comma as thousand separator.

So a new alternative could be

UPDATE wijnen SET Prijs_inc = round(Prijs*1.19*100)/100;


Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question