sql query to update all product prices-CodePudding

I want to update the regular_price and sale price values of all simple and variation products with a SQL query. I need to round the price when updating. to multiples of 5. example:

125.35 - 125.00,
129.10 - 130.00,
128.00 - 130.00,
127.00 - 125.00

CodePudding user response：

UPDATE table SET price = ROUND(price /5) * 5

CodePudding user response：

Use this if you want .00 decimal number:

/* Replace 2 with your decimal count number */
UPDATE table SET regular_price = ROUND(ROUND(regular_price /5) * 5, 2)