Hi Friends,

MYSQL

Just want to share the Concept of Adding a New Field in the Middle of the Table

CREATE TABLE IF NOT EXISTS test
(
id INTEGER AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY(id)
);

Now i want to add a field between id and name in the table.

ALTER TABLE test ADD emp_id INTEGER NOT NULL AFTER id;

__________________
-Murali..

Hi Murali,
I am not clear for your syntax can u explain with suitable examples.

Thanks
V.Vadivelan
Buddies Infotech
Chennai

Mr.vadivelanvaidyanathan,

In Mysql

I mean to say that if we have created a table with the defined structure.

Now i want to add one more field to that table, if am adding a new field that
will be added as the last field in the table.

For Ex:

I want to store the Employee Id,Name and Designation.
My Table Structure is,

CREATE TABLE EMP
(
EMP_ID INTEGER AUTO_INCREMENT COMMENT'Primary Key',
EMP_NAME VARCHAR(100) NOT NULL COMMENT'Employee Name',
EMP_DESIGNATION VARCHAR(100) NOT NULL COMMENT'Employee Designation',
EMP_ACTIVE INTEGER DEFAULT 1 NOT NULL COMMENT'1- Working Currently,Suspended Temporarly,Left the Office',
PRIMARY KEY(EMP_ID)
);


Now i want to include the Employee salary with Name and Designation.
Then

ALTER TABLE EMP ADD EMP_SAL INTEGER NOT NULL;

Now the new added field will be added as the last field in the table,i.e

EMP_ID
EMP_NAME
EMP_DESIGNATION
EMP_ACTIVE
EMP_SAL - New Added Field

But i wish to add the field in order,Employee Salary must be next to the Designation Field, Then the Syntax is,

ALTER TABLE EMP ADD EMP_SAL INTEGER NOT NULL AFTER EMP_DESIGNATION;

Now the Order of the Field will be as follows,

EMP_ID
EMP_NAME
EMP_DESIGNATION
EMP_SAL - New Added Field
EMP_ACTIVE

Its Clear.....

__________________
-Murali..

Thanks Murali now i got it