Number format in mysql update query

Posted on 2005-05-05
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
    LVL 32

    Assisted Solution

    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

    Or maybe you could simply use

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

    Expert Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now