Friday 2 November 2012

MySQL: How to add column to existing table


To add column in existing table, you may refer to the 2 examples below:-


Example I: Add new varchar column to the end of the table
ALTER TABLE `tablename_here` ADD `new_column_name` VARCHAR( 255 ) NOT NULL ;

Example II: Add new integer column after an existing column in table
ALTER TABLE `tablename_here` ADD `new_column_name` INT NOT NULL AFTER `existing_column` ;


MySQL tables are easy to extend with additional columns.


The following SQL command will create a table called contacts as described above:
CREATE TABLE contacts ( contact_id INT(10),
name VARCHAR(40),
birthdate DATE
);

To add a column called email to the contacts table created with a datatype of ARCHAR(80), use the following SQL statement:
ALTER TABLE contacts ADD email VARCHAR(60);
This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;
If you want the new column to be first, use this statement:
ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

No comments:

Post a Comment