CODEWITHSUNDEEP
mysqlsqlsavedecimal
NigelGordon
NigelGordon

Reputation: 59

losing decimal precision when updating MySQL table

I have two columns in my database BuyCommission and SellCommission, both are defined as DECIMAL. However, when my program executes the following:

UPDATE lots 
SET BidValue = '13', Buyer ='2',LotSold = 'S', SellCommission = '1.30', 
    BuyCommission = '1.30'  
WHERE AuctionID = '1' AND LotNumber = '28'

the value shown in the columns is 1, no decimal portion is being saved. Am I using the correct type in defining the column to be DECIMAL?

Upvotes: 0

Views: 874

Answers (2)

Barmar
Barmar

Reputation: 781058

When you specify the DECIMAL datatype, you can specify how many total digits (the precision) and how many digits after the decimal point (the scale), in the form:

DECIMAL(precision, scale)

If you don't specify the scale, it defaults to 0, so it's just an integer. If you want to be able to store 10 digit numbers with 2 decimal places, declare the column to be

DECIMAL(10,2)

See the MySQL documentation of Fixed Point Types

Upvotes: 2

a1ex07
a1ex07

Reputation: 37364

If your fields are decimal you don't need to use single quotes in set: ...., SellCommission = 1.3,.... With single quote you have a string value ('1.3') that implicitly converted to decimal (depends on locale settings , say decimal separator is comma , it may be converted to 1).

Upvotes: 0

Related Questions