How do I add columns using an ALTER TABLE in a specific order?

Question

I have a table sample_tbl with 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?

Answer 1

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);