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

User · Answer

Try ceiling     SELECT Ceiling 45 01   Ceiling 45 49   Ceiling 45 99    http   en wikipedia org wiki Floor and ceiling functions

User · Answer

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

User · Answer

You could use the ceiling function  this portion of SQL code   select ceiling 45 01   ceiling 45 49   ceiling 45 99    will get you  quot 46 quot  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 gt  SELECT CEILING 1 23           - gt  2 mysql gt  SELECT CEILING -1 23           - gt  -1

User · Answer

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

User · Answer

This depends on the database server  but it is often called something like CEIL or CEILING  For example  in MySQL     mysql gt  select ceil 10 5    ------------    ceil 10 5     ------------            11     ------------    You can then do UPDATE PRODUCT SET price CEIL some other field

User · Answer

Combine round and ceiling to get a proper round up   select ceiling round 984 375000   0     gt  984   while  select round 984 375000  0    gt  984 000000   and  select ceil  984 375000    gt  985

User · Answer

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

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

Examples related to sql

Examples related to sql-update

Examples related to rounding