How to update MySQL column = column-number

Question

I have this mysql columns:

points_on | points_off
    0          1.36

And this SQL command:

UPDATE table SET points_off = points_off-{$cash}, points_on = points_on+{$cash} WHERE ...

$cash = "1.36"

If I run this, the table update wrong like this:

points_on | points_off
   1.36      0.0000000143051

How to update this correctly?

I need to move $cash number from points_off to points_on.

How come your `points_on` column value changes whilst you are updating the `points_off` column. That's weird O.o — Shankar Narayana Damodaran, Nov 06 '13 at 09:42

rand · Accepted Answer · 2013-11-06T10:11:03.583

Is the MySQL field type float or double?

According to http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html, the problem is that float values are stored approximated.

You should use decimal, defined as decimal(M,D), where M is the maximum number of digits in the number (e.g., for 0.00 to 9.99, M = 3), and D is the number of digits after the point (e.g., for 0.00 to 9.99, D = 2).

I performed the same operations you tried, got the same problem, and fixed it with decimal.

Beware though that decimal fields require much more storage space than float.

score 0 · Answer 2 · answered Nov 06 '13 at 09:49

0

declare @cash float

set @cash=1.36
update  [dbo].[stkkk] set [pts_on]=[pts_on]+@cash

update  [dbo].[stkkk] set [pts_off]=[pts_off]-@cash

answered Nov 06 '13 at 09:49

vhadalgi

7,027
6
38
67

Im amateur, this is SQL command? `mysql_query("declare ...")`? – David Lences Nov 06 '13 at 09:56
hmm..nope this is from sql-server !! – vhadalgi Nov 06 '13 at 09:58

score 0 · Answer 3 · edited May 23 '17 at 11:50

I believe this stems from how PHP internally represents numbers. From what I can tell, PHP stores numbers as a floating point decimal, which may generate strange output such as yours.

I recommend reading the following StackOverflow articles:

Not sure if it would work 100%, but you might want to try implementing some code to ensure your doing math accurate to two decimal places instead of letting PHP decide. The referenced articles may be of some help.

How to update MySQL column = column-number

3 Answers3