CODEWITHSUNDEEP
mysqlsqldatabase
mrsus
mrsus

Reputation: 5486

Change database column name and its type without losing data

I want to change name and type of column from BIT(1) to INT(1).

I am using MySQL Workbench.

I tried this:

ALTER TABLE table_name ADD new_column_name INT(1)

update table_name
set new_column_name = convert(INT(1), old_column_name)

ALTER TABLE table_name DROP COLUMN old_column_name

But I am getting this error

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(1), old_column_name)'

Upvotes: 6

Views: 24271

Answers (5)

Ksenia Erastova
Ksenia Erastova

Reputation: 11

ALTER TABLE table_name ALTER COLUMN creation_date TYPE timestamp USING creation_date::timestamp;

try this

Upvotes: 0

Antonio Sánchez-Padial
Antonio Sánchez-Padial

Reputation: 186

INT(1) doesn't seem to be a valid type for conversion.

Furthermore, the right syntax is CONVERT(<element>, <type>).

You can find more in detail here.

Upvotes: 2

user330315
user330315

Reputation: 

ALTER TABLE table_name ADD new_column INT(1);

update table_name
  set new_column = case when old_column then 1 else 0 end;

ALTER TABLE table_name DROP COLUMN old_column;

or simply use:

ALTER TABLE table_name MODIFY COLUMN old_column int;

Upvotes: 5

Bere
Bere

Reputation: 1747

ALTER TABLE table_name CHANGE old_column_name new_col_name varchar(20) not null;

I put varchar as an examaple. you can change to any data type you like. You can also add constraints after that.

Upvotes: 4

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try something Like this.

ALTER TABLE table_name ADD new_column_name INT

update table_name
set new_column_name = convert(int,convert(varchar(1), old_column_name))

Upvotes: 2

Related Questions