Inserting New Column in a Table at MySQL

Asked By Tariku Dagmar 240 points N/A Posted on - 05/10/2011

I have a Database at my store. Unfortunately, I want to add new column to one of my tables. What I am thinking is to use the command DELETE TABLE and replace it with a new one but I want to save time editing and filling again the data.

Is there a way to just insert a new column which could not affect or change my stored data? Please help. Here is the data in my table. 

Receipt_code | Month | Account_No | Amount | Date

 

I want to insert Payment_Code here. Can you suggest easier way?

• Status: Open
• Question Views: 1369
• Answer Count: 3
• Vote Up 24 Vote Down

• Answer Accepted: Yes
• Question Category: MySQL Server

Best Answer by Mikael H

Go To Solution

Best Answer

Best Answer

Answered By Mikael H 5 points N/A #89797

Inserting New Column in a Table at MySQL

It’s a bad idea to delete your existing table and create a new table for adding a column. The easiest and faster way is to use Alter table command in MySQL to modify your table structure.

 

You can Add a new column with the help of ALTER TABLE command. The syntax of ALTER TABLE command to add new column is:

 

ALTER TABLE [tablename] ADD COLUMN [column_name] datatype (size);

 

As you want to add Payment_Code column name in your table. You have not given table name so I assume ‘Receipt’  is the name of the table. So the command will be :

 

ALTER TABLE Receipt ADD COLUMN Payment_Code varchar (20);

 

I am assuming varchar is data type of your column, you can change it if different. To get more understanding of ALTER command we can take look at whole functionality of this command. It is a powerful SQL command that allows you to modify all parts of a table definition. This includes basic operations like adding and deleting columns, but also advanced functionality, like migrating your table data between storage engines.

 

The ALTER TABLE syntax

 

MySQL’s ALTER TABLE enables you to change the structure of an existing table. As mentioned, you can use this functionality to add or delete columns, but also perform operations like: creating or deleting indexes, changing the data type of an existing column, or renaming columns or tables.

 

You can also change the comment for the table, as well as the storage engine (type) of the table.

 

The syntax for many of the allowable alterations is similar to the clauses supported by the CREATE TABLE statement. This includes table_option modifications, for options such as ENGINE, AUTO_INCREMENT, and AVG_ROW_LENGTH.

 

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the “copy”, and then the original table is deleted and the new one is renamed.

 

While an ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

 

If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name. There is no need to create a temporary table. The complete syntax and options are shown below:

 

ALTER [IGNORE] TABLE tbl_name

alter_specification [, alter_specification] …

alter_specification:

ADD [COLUMN] column_definition [FIRST | AFTER col_name ]

| ADD [COLUMN] (column_definition,…)

| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…)

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_name,…)

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,…)

| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,…)

| 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 column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP {INDEX|KEY} index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| table_option …

About Mikael H

Questions
0

Answers
9

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/14/2011
• Question Category: MySQL Server

Answered By anggaphh 0 points N/A #89798

Inserting New Column in a Table at MySQL

It is sometimes necessary to change the structure of a table, like what you experienced. For such purposes, use the ALTER TABLE statement. In your case, you want to add columns Payment_Code, here's the easiest way:

 

ALTER TABLE [your_tablename] ADD Payment_Code VARCHAR (20);

 

By way as above, the column named Payment_Code with type VARCHAR (20)(maximum 20 characters) in itself is added to your table (here you do not tell the name of your table). So, when you type DESC table_name, it appears that the column Payment_Code is placed at the end.

 

Addition of columns can also be done in order to be placed after the location of a particular column. Example:

 

ALTER TABLE [your_tablename]

ADD Payment_Code VARCHAR (20) AFTER Receipt_code;

 

In this way, column Payment_Code is placed after column Receipt_code. I hope my simple and brief explanation may help you.

About anggaphh

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/14/2011
• Question Category: MySQL Server

Answered By waleed107 10 points N/A #89799

Inserting New Column in a Table at MySQL

Hi,

The easiest way to add column in your existing table is to use alter table command in order to add a new column. There is no need to delete and create a new table.

The syntax of this command is:

ALTER TABLE [table_name]

ADD COLUMN [column_name]

datatype (size)

Once you use this syntax it will automatically add a new column in your existing table.

About waleed107

Questions
1

Answers
10

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 02/07/2013
• Question Category: MySQL Server