Example: Removing the “.0” from an Excel import into MySQL
- MYTABLE – The MySQL Table Name
- FIELDNAME – The field you wish to edit the data
- Make sure you have a primary key defined, I usually set mine as “id” for the table. If you are using a different name for the primary key ID, replace the “id” with the different name.
UPDATE MYTABLE AS T1, MYTABLE as T2 SET T1.FIELDNAME = replace(T2.FIELDNAME,’.0′,”) WHERE T2.id = T1.id;
Before running this query, make sure you make a backup in case it doesn’t work correctly.