Updating values in a table using ceiling

Question

Updating values in a table using ceiling

Bne

SSC Journeyman

Points: 86
More actions
July 4, 2004 at 11:26 pm

#180809

I'm trying to update values in a table that stores costs of various items. I need to round all the costs up where they have a decimal value eg:
100.53 rounds up to 101.00
100.26 round up to 101.00
I can use the ceiling function to return all the correct values using the following:
select ceiling(cost)
from table_name
I'm now having trouble trying to update the table with the new values returned using the ceiling command as I need to round the cost values for all rows (70000 odd). Anyone got any solutions for this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246716 More actions · Answer 1

What sort of trouble are you having? Do you want to batch the updates?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bne SSC Journeyman Points: 86 More actions · Answer 2

Basically, I'm just trying to replace all the values in the fee column with the the new valules I return when I use the ceiling(fee) comand. When I try to set the fee value with the new value using ceiling(fee), I run into a problem as there are multiple values being returned by the select command. This is where I've got so far:

update fee_maxima_benchmarks

set fee = (

select ceiling(fee)

from fee_maxima_benchmarks

);

I'd thought about using a temp table, but haven't really done that before, is this the way to go or is there a simple method?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

You've made it a bit difficult on yourself... the following should do nicely and should do 70,000 records in something less than 10 seconds on a good server...

UPDATE Fee_Maxima_Benchmarks
SET Fee = CEILING(Fee)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bne SSC Journeyman Points: 86 More actions · Answer 4

Thanks Jeff, my brains mush from the 2 months I've just spent in Greece. This worked perfectly. Simple is always best. Cheers.

Phil Parkin SSC Guru Points: 246716 More actions · Answer 5

I would add a WHERE clause to this, along the lines of

UPDATE Fee_Maxima_Benchmarks

SET Fee = CEILING(Fee)

WHERE Fee CEILING(Fee)

The slight amount of extra processing involved is worth it to reduce the potential number of disk writes. It also means that you can rerun the query at any time in the future without re-writing all the records already fixed.

Cheers

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bne SSC Journeyman Points: 86 More actions · Answer 6

Bne

SSC Journeyman

Points: 86

July 5, 2004 at 5:45 pm

#513364

great thanks!