mysql field ALTER to decimal(10,2)

need to change field to decimal(10,2) and round up

[makimoto]

hi,
i have a sql statement generated by phpmyadmin where i changed the field value of a table from "float" to "decimal(10,2)". here it is:

ALTER TABLE `BMG` CHANGE `I_PLQ` `I_PLQ` DECIMAL( 10, 2 ) DEFAULT '0'

but i need it to also round the existing values up. for example: 2.4875 to 2.49
but for some reason it rounded it to 2.48, and i don't know how to change the statement so it will round values up for existing values in the field. is this even possible? if not, how would you handle it? please advise,
TIA,
makimoto

[coopster]

Rather than change the column you are better off adding a new column, running an update statement to populate the new column with your ROUND [dev.mysql.com]ed value, then drop the old column. Before you drop the old column, you may even want to run a SELECT statement to find rows where the OLD/NEW columns are not equal.

[makimoto]

thanks coopster,
just for the sake of documentation, here is how i got it to work (using phpmyadmin):

i added a new field using phpmyadmin and gave it the same attributes as the field i was copying from (so i cheated a bit here).
then i used two UPDATE statements to get me where i wanted to be (i probrably could have used one, but i can't think of how). the first one was:

UPDATE table
SET field_2=field_1;

and the second one was:

UPDATE table
SET field_2 = ROUND( field_2, 2 );

this suceeded in creating a new field_2, copying the values from field_1 to field_2, and then rounding the values in field_2 up two decimal places. so in the end, i did not even have to change the type of field from "float" to "decimal(10,2)". for example, now 2.4875 = 2.49 NOT 2.48.