CODEWITHSUNDEEP
mysql
Anil
Anil

Reputation: 2455

Convert char(5) to tinyint(4) in mysql

I'm trying to insert rows from one table to the other. In the first table, the datatype of one column is char(5), but the same column has tinyint(4) datatype in the second table. When i run the insert query, it says

Incorrect integer value: '' for column 'x' at row 258

I cannot alter or modify the datatype now as it violates some constraints. Is there a way to use cast or convert char to tinyint?

Thanks.

Upvotes: 2

Views: 930

Answers (1)

D Mac
D Mac

Reputation: 3809

You probably want something like this:

INSERT INTO newtable
SELECT CASE WHEN x = '' THEN 0 ELSE x END
FROM oldtable

I'm assuming that you want blanks to turn into zeros? If not, then provide the integer value you want blanks to have.

If there are other exceptions, use more alternatives in the CASE expression.

Upvotes: 3

Related Questions