Changing Position Of A Column In An Existing Table
For some reason, today I need to change position of a column in an existing table. Surprisingly, the only solution I ended up with is to use a temp table. I first execute following sql command;
CREATE TABLE temp AS SELECT column1, column3, column2 from table
By changing column order in select part the position of the column is changed. Then I executed following command and recreate table using temp.
CREATE TABLE table AS SELECT * from temp
I think there should be a more elegant solution to this. My table sizes small so I could do it this way but using this solution with big data will certainly couse some headaches. If somebody has a better solution please feel free to specify it in a comment.
Hi Umit;
Thanks for the quick tip. There is not enough information on the web about this topic.
You may be right about the data sizes but if you are doin it on the prod. env. than it is too late. But if it is in development time than the data will not be so much important and you can add a WHERE criteria to decrease the data size of your temp table and by that way it will execute much more faster.
Remove comment