Restructuring a table

Thread view:

Ike - 19 Dec 2003 18:42 GMT

Suppose I have a table, and it is populated with many rows already. However,
I realise I structured something wrong in my table. I dont want to have to
go back and repopulate the entire table.

For example, suppose I have a field:

"inoutstatus INTEGER(3),"

Which I now realize should be :

"inoutstatus CHAR(1),"

Is there a way I can change the entire table without losing the other
records in each row? I can go back in and repopulate the inoutstatus fields,
but I really would like to know if there is merely some type of SQL command
I can issue to restructure the table this way.

Thanks, Ike

Reply to this Message

Sampsa Sohlman - 19 Dec 2003 22:53 GMT

> Suppose I have a table, and it is populated with many rows already. However,
> I realise I structured something wrong in my table. I dont want to have to
[quoted text clipped - 12 lines]
> but I really would like to know if there is merely some type of SQL command
> I can issue to restructure the table this way.

I don't know which is your database platform, but I would quess that it
would go someting like this.

SELECT CAST( inoutstatus AS INT ), here_other_columns INTO new_table
FROM old_table

Instead of CAST you might have you some other function which is specific
to your database like CONVERT

- Sampsa

Signature

-------------------------------------------
// Sampsa Sohlman //
// My email can be found on my homepage //
// http://sampsa.sohlman.com //
-------------------------------------------

Reply to this Message

Silvio Bierman - 20 Dec 2003 00:23 GMT

> Suppose I have a table, and it is populated with many rows already. However,
> I realise I structured something wrong in my table. I dont want to have to
[quoted text clipped - 14 lines]
>
> Thanks, Ike

Look into ALTER TABLE statements. Beware of conversion issues when changing
the type of a column.

Silvio Bierman

Reply to this Message

brougham5@yahoo.com - 20 Dec 2003 00:40 GMT

>"inoutstatus INTEGER(3),"
>
>Which I now realize should be :
>
>"inoutstatus CHAR(1),"

Look for the commands drop and alter. Drop the column, and then alter the
table to add a new column. You may need to do that in two stages...add a
column that accepts nulls, add your values, and then alter it again not to
accept null values if that's what you want the end result to be.

Reply to this Message

Restructuring a table

Free Magazines