[sql] Round up value to nearest whole number in SQL UPDATE

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

If you want to round off then use the round function. Use ceiling function when you want to get the smallest integer just greater than your argument.

For ex: select round(843.4923423423,0) from dual gives you 843 and

select round(843.6923423423,0) from dual gives you 844

Try ceiling...

SELECT Ceiling(45.01), Ceiling(45.49), Ceiling(45.99)

http://en.wikipedia.org/wiki/Floor_and_ceiling_functions

Ceiling is the command you want to use.

Unlike Round, Ceiling only takes one parameter (the value you wish to round up), therefore if you want to round to a decimal place, you will need to multiply the number by that many decimal places first and divide afterwards.

Example.

I want to round up 1.2345 to 2 decimal places.

CEILING(1.2345*100)/100 AS Cost

Combine round and ceiling to get a proper round up.

select ceiling(round(984.375000), 0)) => 984

while

select round(984.375000, 0) => 984.000000

and

select ceil (984.375000) => 985

This depends on the database server, but it is often called something like CEIL or CEILING. For example, in MySQL...

mysql> select ceil(10.5);
+------------+
| ceil(10.5) |
+------------+
|         11 | 
+------------+

You can then do UPDATE PRODUCT SET price=CEIL(some_other_field);

For MS SQL CEILING(your number) will round it up. FLOOR(your number) will round it down