Home / Database Structure / Tables / Question No: 73

I want to add new column buyer_discount (integer/not null type) into my existing table buyers. How I will add this using query in MySQL?

You may use ALTER statement as follow:

ALTER TABLE `buyers` ADD `buyer_discount` INT NOT NULL;

As per above example, when you add a new column to a table, MySQL places it after all existing columns. This is the default placement unless you specify otherwise. To indicate that MySQL should place the new column in a specific position within the table, append either the keyword FIRST or the keyword-identifier combination AFTER column_name to the column definition. For example, assume that you had executed this ALTER TABLE statement instead of the previous one:

ALTER TABLE `buyers` ADD `buyer_discount` INT NOT NULL FIRST;

The FIRST keyword tells ALTER TABLE to place the new column before all existing columns (in the "first" position). Using AFTER column_name tells ALTER TABLE to place the new column after a specific existing column. For example, to place the new buyer_discount column after the existing FirstName column, you would issue this statement:

ALTER TABLE `buyers` ADD `buyer_discount` INT NOT NULL AFTER FirstName;