MySQL Error 1366 when trying to change column data type to INT

Question

I can't figure out why I can't change a column's data type from TEXT to INT. Here is the script I'm using.

ALTER TABLE covidworlddata.coviddeaths
ALTER COLUMN total_deaths INT;

Error 1366: Incorrect integer value: '' for column 'total_deaths' at row 1 SQL Statement: ALTER TABLE covidworlddata.coviddeaths CHANGE COLUMN total_deaths total_deaths INT(255) NULL DEFAULT NULL

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT' at line 2

Is it because the first row of the csv is the column names (e.g. total_deaths) as seen below? Source data csv

Answer 1

You have a syntax error, so check the syntax. As a minimum you need this

ALTER TABLE covidworlddata.coviddeaths
CHANGE COLUMN total_deaths total_deaths INT;

That's old column name, new column name (which is the same here) and then the rest of the column definition.

Reference: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

Answer 2

1. ALTER COLUMN does not allow to change the datatype. It only allows you to set/drop DEFAULT values and change the visibility of the column. You must use either CHANGE COLUMN or MODIFY COLUMN. See ALTER TABLE Statement.

This problem causes #1064.

2. Your column which you want to alter have string datatype now and contains empty string as a value. When you modify the column definition the server must convert the datatype for existing values. Empty string cannot be converted to numeric datatype correctly. You must update your table prevously and set the column values to the value which can be converted correctly (for example, this can be '0' or NULL).

This problem causes #1366.