CODEWITHSUNDEEP
mysqlsql
BellamyStudio
BellamyStudio

Reputation: 771

Remove 20% from stored price in MySQL field, using SQL query?

I'm working on a site with a client they've just realised that they've given me all of their prices inclusive of sales tax (20%) - is there a way for me to iterate through the price fields and update the value to remove 20% from every entry? In a sql query?

Upvotes: 3

Views: 2345

Answers (2)

WWW
WWW

Reputation: 9870

You'll have to fill in the blanks with the actual table name and price field, but that's basically all you'd have to do.

UPDATE table SET price = price / 1.2

(edited to fix my math)

If you want to make sure you only have 2 decimal places, you could use MySQL's ROUND() function like this:

UPDATE table SET price = ROUND(price / 1.2, 2)

You might consider changing the data type of the price field to a DECIMAL(9,2) type, depending on how high your prices might ever get.

Upvotes: 5

Álvaro González
Álvaro González

Reputation: 146630

UPDATE foo SET price = price / 1.2

Update: As about rounding, you have ROUND(X, D).

Upvotes: 4

Related Questions