Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb Unanswered: DELETING COLUMN from a table

    Hi all , i want to delete the column name from the table using DELETE statement.

    I tried with the below query, but it didn't work ,i feel there is something wrong in this syntax.
    Here columnname==name of the column
    tabname1 is the table name.

    kindly suggest me the modifications to this query


    DELETE columnname
    WHERE columnname IN
    (
    SELECT columnname
    FROM tabname1
    )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DELETE is not how to remove a column.
    Try using ALTER TABLE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Try:
    Code:
    ALTER TABLE tabname1 DROP COLUMN columnname1;

    PS: You may need to set the values for that column to NULL before removing the column.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Oct 2006
    Posts
    83
    in a particular case in one of my application i have to use DELETE statement to delete the column

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DELETE
    Purpose
    Use the DELETE statement to remove rows from:
    * An unpartitioned or partitioned table
    * The unpartitioned or partitioned base table of a view
    * The unpartitioned or partitioned container table of a writable materialized view
    * The unpartitioned or partitioned master table of an updatable materialized view

    I challenge you to post a working code example where DELETE successfully "delete the column".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Do you, perhaps, want to UPDATE this column to NULL? It would, sort of, "delete" this column.

    If so, it could be done like this:
    Code:
    UPDATE some_table SET 
      this_column = NULL;
    You'd add the WHERE clause if necessary.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    or you can mark it as unusable so it wont show up in a select * statement. some mark columns unusable and then drop them (especially for large tables):
    PHP Code:
    alter table XYZ set unused column col1;

    alter table XYZ drop unused columns
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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