asked on

Update and data manipulation of a number field

I have a "Price" field that contains values like : 50.22, 8.78, 68.19, etc.

I want the last 2 digits after the decimal point to be rounded like this :

If : .01 to .29 = .29

If : .30 to .49 = .49

If : .50 to .99 = .95

So if Price is "29.14", it would be "29.29", . . . "18.78" would be "18.99", etc.

UPDATE Nop_ProductVariant
SET Price = ?

Also, I want to raise all Prices by 18%, like :

UPDATE Nop_ProductVariant
SET Price = round(Price * 1.18, 2)

Can I combine these 2 queries at once? Or should I run 2 separate queries? Thanks

John Claes

You can do this in 1 big Update script. and 3 different scripts

3 scripts : 
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.29 where price % 1 < 0.29
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.49 where price % 1 < 0.49 and price % 1> 0.29
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.95 where price % 1 >0.49

1 script
UPDATE Nop_ProductVariant 
SET Price = round(Price, 0, 1) + 
      Case when  price % 1 < 0.29 then 0.29
           when price % 1 < 0.49 and price % 1> 0.29 then 0.49
           else 0.95
      End

Open in new window

ASKER CERTIFIED SOLUTION