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

x
?
Solved

Number format in mysql update query

Posted on 2005-05-05
3
Medium Priority
?
1,781 Views
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.
0
Comment
Question by:Geerd
  • 3
3 Comments
 
LVL 32

Assisted Solution

by:Batalf
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!?

example:

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

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

example:

select format(Prijs_inc,2) from wijnen
0
 
LVL 32

Accepted Solution

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

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

Expert Comment

by:Batalf
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;

0

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