alter column of table containing data

[Thorsten3]

I need to alter a column of a table, containing data allready, from varchar(4) to varchar(6), because I need to store longer strings than I expected. The new table should contain all the data as the old one and should have the same name as the old one.
How can I copy the structure and the data of a table?
I also need to add a column to a table filled with data. This will probably be done the same way?

 

[Otto]

Hi!

Usually I do it on this way:

1. alter table TABLENAME add TMPFIELD varchar(6)
2. update TABLENAME set TMPFIELD=OLDFIELD
3. alter table TABLENAME drop OLDFIELD, add OLDFIELD varchar(6)
4. update TABLENAME set OLDFIELD=TMPFIELD
5. alter table SZEM drop TMPFIELD

I hope this help you. Good luck.
Bye, Otto.

 

[svanels]

The steps you have to follow are:

1)Add a temporary column (same type as the column to be changed)

2)Copy the data to the temporary column

3)Drop the column to be changed

4)Add a column (new structure, same name)

5) Copy data from Temporary Column

6) Drop temporary column

The interbase sintaxe is written bellow, for other languages it should be similar

1) ALTER TABLE MYTAB ADD TEMP_DEF VARCHAR(4);

2) UPDATE MYTAB
SET TEMP_DEF = COLUMN_TO_BE_CHANGED;

3) ALTER TABLE MYTAB DROP COLUMN_TO_BE_CHANGED;

4) ALTER TABLE MYTAB ADD COLUMN_TO_BE_CHANGED VARCHAR(6);

5) UPDATE MYTAB
SET COLUMN_TO_BE_CHANGED = TEMP_DEF;

6) ALTER TABLE MYTAB DROP TEMP_DEF;

Hope this serves

S. van Els
SAvanEls@cq-link.sr

 

[carp]

If you are using Oracle, you can do the following:

ALTER TABLE my_table MODIFY column_to_be_changed VARCHAR(6);

To add a column, you would issue the following command:

ALTER TABLE my_table ADD new_column_name new_datatype;