How to modify datatype of a column using alter table command

Database
Fan

How to modify datatype of a column using alter table command

Hi,

Can anyone help me reg this

Ex:i am having a table

Emp

columns:
Empname varchar(10)
Empno integer
Salary decimal(10,2)

i want to change the salary decimal (10,2) to decimal (15,2) using alter table command.
IF i already have recors in that table can i modify that.

Regards,
Prabaharan.M
13 REPLIES
Enthusiast

Re: How to modify datatype of a column using alter table command

alter table emp
add salary decimal(15,2);

This will alter the col length, the length can be increased but cannot be decreased.

Fan

Re: How to modify datatype of a column using alter table command

Thanks for ur reply but i need to modify the datatype of same column,I dont want to add new column.
Enthusiast

Re: How to modify datatype of a column using alter table command

User ALTER TABLE..ADD option.

You can change data type attributes for existing columns.
There are some restrictions.
You may want to refer DDL manual for more information.

Vinay
Enthusiast

Re: How to modify datatype of a column using alter table command

Prabaharan,
the add option will not add new coloum in this case.
it will just modify the length of the column.
Fan

Re: How to modify datatype of a column using alter table command

Ya its working thanks,
whether its possible to change from char to varchar,etc...

Re: How to modify datatype of a column using alter table command

There is one constraint to the above way of modifying it. If you want to increase the length of a column from varchar (100) to varchar (102) it will alter the column length, but, if you want to reduce the length of the column using ADD in Alter command, it gives an error:
"3558: Cannot alter specified attribute(s) for ."

Please validate and verify. Also let me know, what command should I use to decrease the length of a column.

Re: How to modify datatype of a column using alter table command

Hi this alter.add command is working for the columns at the end of the table however it is not working for the last but columns. Is there any a way to increase the size of the data types of the last but columns.
N/A

Re: How to modify datatype of a column using alter table command

how to change  datatpe in table?

create table tablename(eid  integer,ename varchar(30));

anyone can help, i am beginner of td.

the table have eid integer , i would like to change eid varchar(10).

how overcome this query

please td friens give request

Junior Contributor

Re: How to modify datatype of a column using alter table command

You can't do that change using ALTER TABLE, you need a new table.

Dieter