Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    48

    Post Unanswered: changing position of column

    let suppose we have a table that contains 3 column, and i add one more column which become the forth one. Forth column will be the last column of that table,
    can i change its position of the forth column that become the first one of the table.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not directly, no. Normally there is no need to do this. If you wanted you could do this:
    Code:
    create table mytable_copy as select col4, col1, col2, col3 from mytable;
    
    drop table mytable;
    
    rename mytable_copy to mytable;
    Of course, you'd need to take care of dependant objects like indexes, constraints, triggers too.

    Unless there is a very good reason for doing this, it is best to get used to the fact that new columns get appended to the end of the table and don't worry about it.

  3. #3
    Join Date
    Feb 2009
    Posts
    48
    thanks folks

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    i could never figure out why people are so hung up on the actual default order of columns within a table. Can anyone give me a good reason other than "I like it to look this way"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Probably because SELECT * FROM ... looks prettier when columns are ordered than not. Because, who'd write all columns (ordered!) into <column list> of the SELECT <column_list> FROM ... statement?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Littlefoot View Post
    Probably because SELECT * FROM ... looks prettier when columns are ordered than not. Because, who'd write all columns (ordered!) into <column list> of the SELECT <column_list> FROM ... statement?
    I know, it's so bothersome to specifically select the columns you want.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Littlefoot View Post
    Because, who'd write all columns (ordered!) into <column list> of the SELECT <column_list> FROM ... statement?
    anyone following best practices for SQL, that's who

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, someone is happy with good enough practices, not the best.

    Unfortunately, sooner or later good enough might turn into quite bad and things that could have been done long ago must be done now. And sometimes it hurts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •