How to move decimal place to the left on prices

[Hendrickson]

Hi I wondered if anyone ever moved a row's value from say 10000.00 over to 100.00. So move the decimal place two points to the left so I can lower prices which out having to manually do this.

There must be a MySQL command for this?

[gurpreet]

You could create a php script for this, I'm terrible at SQL. In the php page include your globals (just for a one time use then delete the file) and select the items and such. Then use a while loop and make a variable like:

$newprice = $whilevar['itmbuyprice'] / 100;

Then insert the $newprice into the items table. However I would suggest making it more complicated by checking for certain values like if the price is between 0 and 10,000 it will divide by 1 amount, 10,000 - 500,000 another etc.

[bluegman991]

UPDATE `tbl` SET `field`=`field`*10

^^ to move decimal to right (raises value)

 

UPDATE `tbl` SET `field`=`field`/10

^^to move decimal to left (lowers value)

Just add a zero to the 10 for each decimal place you want to move over. Warning both queries will update all rows in table. Incase that is or is not what you want.

[lucky3809]

moving the decimal place edit the field...

either 4,1 4,2 4,4 ....

4,1 would come out as 1000.0

4,2 would come out as 1000.00

4,4 would come out as 1000.0000 and so forth...

if your wanting it to go to 100.00 and nothing over 900.00

you would edit it to..

3,2

your decimal is the comma.

Edited October 29, 2011 by lucky3809

[bluegman991]

He doesn't want to change how many decimal places he has. As it says in his post he wants to lower prices by changing decimal places.

10,000.00 -> 100.00 [From his original post]

The query for this one would be:

UPDATE `tbl` SET `field`=`field`/100

^^The above moves shifts the decimal 2 places to the left

It will change:

9,900.00 -> 99.00

99,000.00 -> 990.00

999,000.00 -> 9,990.00