rename a column?

[dadamssg]

how do i rename a column in my db that has existing data?

[Ken2k7]

ALTER tablename CHANGE column_name new_column_name column_definition

[dadamssg]

so it would be somethin like

 

ALTER personal_info CHANGE dob date_of_birth DATETIME

?

 

with the old and new column names right next to each other?

[Axeia]

Where'd that DATETIME come from all of a sudden, that's not simply changing a name.

[dadamssg]

i was including the column definition....????

[Ken2k7]

Yup, that works.

[PFMaBiSmAd]

It's part of the syntax -

 

12.1.7. ALTER TABLE Syntax

 

ALTER [ONLINE | OFFLINE] [iGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...

 

alter_specification:

    table_option ...

  | ADD [COLUMN] col_name column_definition

        [FIRST | AFTER col_name ]

  | ADD [COLUMN] (col_name column_definition,...)

  | ADD {INDEX|KEY} [index_name]

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]] PRIMARY KEY

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        UNIQUE [iNDEX|KEY] [index_name]

        [index_type] (index_col_name,...) [index_option] ...

  | ADD FULLTEXT [iNDEX|KEY] [index_name]

        (index_col_name,...) [index_option] ...

  | ADD SPATIAL [iNDEX|KEY] [index_name]

        (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        FOREIGN KEY [index_name] (index_col_name,...)

        reference_definition

  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] old_col_name new_col_name column_definition

 

If you don't need to do this inside of a program, you can probably directly rename your column in your favorite database management tool.