Search code examples
mysql

#1265 - Data truncated for column

I have a table with a b_mag field that contains about 13000 records which is VARCHAR but I need FLOAT because I have to sort them numerically and do other things. The values that b_mag contains are all of the type 12.45, 9.5, 10.5 and some empty fields. I created a new b_mag_num field of type FLOAT and ran this query:

UPDATE `vega_ngc_ic_messier_catalog` SET `b_mag_num` = `b_mag` WHERE `ID`=`ID`

but I get this error:

#1265 - Data truncated for column 'b_mag_num' at row 8

I don't understand why the data is truncated, FLOAT can contain the type of data that is passed to it.

Thanks to anyone who can help me.

Solution

  • To avoid truncations, it is probably better to update with a cast so to specify the decimal value precision.

    UPDATE `vega_ngc_ic_messier_catalog` SET `b_mag_num` = CAST(`b_mag` AS DECIMAL(10,6)) WHERE COALESCE(`b_mag`, 0)<>0

    Alternatively, you could also alter the column type.