A.7.2 How To Change the Order of Columns in a Table

The whole point of SQL is to abstract the application from the data storage format. You should always specify the order in which you wish to retrieve your data. For example:

SELECT col_name1, col_name2, col_name3 FROM tbl_name;

will return columns in the order col_name1, col_name2, col_name3, whereas:

SELECT col_name1, col_name3, col_name2 FROM tbl_name;

will return columns in the order col_name1, col_name3, col_name2.

If you want to change the order of columns anyway, you can do it as follows:

1. Create a new table with the columns in the right order.
2. Execute INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.
3. Drop or rename old_table.
4. ALTER TABLE new_table RENAME old_table.

You should never, in an application, use SELECT * and retrieve the columns based on their position, because the order and position in which columns are returned cannot may not remain the same (if you add/move/delete columns). A simple change to your database structure would then cause your application to fail. Of course SELECT * is quite suitable for testing queries.

User Comments

Add your own comment.