Alter table query in Informix DB

Question

DBMS: Informix.

I have a table sample_tbl with 5 3 fields, that are:

• user_id,
• user_name,
• user_email

Now I want to add the following fields into sample_tbl with one single alter query to add the columns. The new fields are:

• user_phone_no,
• user_location,
• user_password

I want to add the user_phone_no after the user_id, and I want to add the user_location, user_password after the user_email field, and all with a single query. Any suggestion for this?

Solution

According to IBM online docs Informix v10 allows ALTER TABLE ADD column BEFORE existing_column.

So something like this could work (I don't have Informix connectivity on this computer)...

ALTER TABLE sample_tbl
ADD user_phone_no varchar(10) BEFORE user_name,
ADD user_location varchar(10),
ADD user_password varchar(10);

Comments

[Jonathan Leffler]

Should work fine - well done on the manual bashing. The notation has been in every version of Informix from 1.10 in 1985 to 11.70 in 2010.

[pascal]

I wasn't bashing the manual. I just googled the answer, and there was no indication of the version in the question... I just played with Informix from v4 to v9.