CODEWITHSUNDEEP
mysql
Brian G
Brian G

Reputation: 409

MySQL convert column datatype from VARCHAR to INT

I have a MySQL table with a VARCHAR(25) column that I want to convert to INT. All of the data in the column is either integer or blanks. I have tried this command:

ALTER TABLE ip MODIFY isp INT UNSIGNED NOT NULL;

Unfortunately, since some of the existing rows are empty I get this error:

ERROR 1366 (HY000): Incorrect integer value: '' for column 'isp' at row 2

Thanks for assistance.

Upvotes: 31

Views: 32773

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

before altering your table, try to update your values.

The goal is to set a '0' value in the fields where you have empty values (which can't be converted to int)

update ip
set isp = '0' where trim(coalesce(isp, '')) = '';

If isp was not nullable, you can remove the coalesce function.

update ip 
set isp = '0' where trim(isp) = '';

Upvotes: 29

Related Questions