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 …
