Round up value to nearest whole number in SQL UPDATE

Question

I'm running SQL that needs rounding up the value to the nearest whole number.

What I need is 45.01 rounds up to 46. Also 45.49 rounds to 46. And 45.99 rounds up to 46, too. I want everything up one whole digit.

How do I achieve this in an UPDATE statement like the following?

Update product SET price=Round 

Answer 1

You could use the ceiling function; this portion of SQL code :

select ceiling(45.01), ceiling(45.49), ceiling(45.99); 

will get you "46" each time.

For your update, so, I'd say :

Update product SET price = ceiling(45.01) 

BTW : On MySQL, ceil is an alias to ceiling ; not sure about other DB systems, so you might have to use one or the other, depending on the DB you are using...

Quoting the documentation :

CEILING(X)

Returns the smallest integer value not less than X.

And the given example :

mysql> SELECT CEILING(1.23);         -> 2 mysql> SELECT CEILING(-1.23);         -> -1