2

I have an ITEMS table with a PRICE column. I added 4% to the price using the built-in functions in the application, but that leaves the result not in a friendly formats for retail. I want all the prices to end with 8 (so $24.93 becomes $24.98)

I need to replace the PRICE with a PRICE rounded to the next 10th, and then subtract .02. This would make 24.93 becomes 25 then subtract .02, making it 24.98.

Could someone help with crafting a SQL query to do this?

Perhaps something like UPDATE ITEMS SET PRICE=(ROUND (PRICE,2)-.02) ?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
john castaldi
  • 21
  • 1

1 Answers1

2

If you want to always round up, you should use ceil, not round. Note that ceil doesn't have a precision argument, so you'll have to implement this yourself by multiplying the price by 10, ceiling it, and then dividing it back by 10:

UPDATE items SET price = CEIL(price * 10) / 10 - 0.02

If you're anxious to actually do this, you could query this expression to verify it gives the correct result before actually applying the update:

SELECT price, CEIL(price * 10) / 10 - 0.02 AS corrected_price
FROM   items
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • mysql's ceil doesn't take a second parameter (unlike round, which does), so you need to do `ceil(price*100)/100` – ysth Dec 24 '21 at 23:54
  • it's actually interbase SQL (Firebird 3) – john castaldi Dec 25 '21 at 02:15
  • @johncastaldi so please edit your question and remove the mysql tag. Anyway, the Firebird also has a similar [`ceil`](https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-scalarfuncs-ceil) function, so the answer still stands – Mureinik Dec 25 '21 at 05:57
  • You need to multiply and divide by 10, not 100, to achieve the desired result. – Mark Rotteveel Dec 26 '21 at 08:12
  • @MarkRotteveel good catch, thanks. Got the second decimal place of 0.02 stuck in my head... :-( [edited and fixed] – Mureinik Dec 26 '21 at 08:54