Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Cergy
    Posts
    65

    Question Unanswered: check if a field exists in a table

    I want to write a script to alter a table: adding a field

    Is it possible to do a condition to see if the field already exists:

    IF NOT EXISTS myfield THEN ...

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    where not exists
    (select 1 from user_tab_columns
    where table_name = '<table name>'
    and column_name = '<column name>');

  3. #3
    Join Date
    Sep 2010
    Posts
    3

    Question

    I've tried to use WHERE NOT EXISTS along with my ALTER TABLE and it's not working. Is the following script correct?

    Code:
    ALTER TABLE <tablename> ADD <columnname> <columntype>
    WHERE NOT EXISTS
    (SELECT * FROM user_tab_columns
    WHERE table_name = '<tablename>'
    AND column_name = '<columnname>');

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by TiagoRocha View Post
    I've tried to use WHERE NOT EXISTS along with my ALTER TABLE and it's not working. Is the following script correct?
    No, you can't use a WHERE clause for the ALTER TABLE command. You need to put this into a pl/sql block.

    Something like:
    Code:
    DECLARE 
      col_count  integer;
    BEGIN 
      SELECT count(*)
        into col_count
      FROM user_tab_columns
      WHERE table_name = '<tablename>'
      AND column_name = '<columnname>';
      
      IF col_count = 0 THEN 
         EXECUTE IMMEDIATE 'ALTER TABLE bla ADD ...';
      END IF;
    END;

  5. #5
    Join Date
    Sep 2010
    Posts
    3

    Thumbs up

    So, I see there is just no way to do this in a T-SQL way...

    Thank you very much!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So, I see there is just no way to do this in a T-SQL way...
    T-SQL??????

    Please NOTE this is an ORACLE forum?
    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.

  7. #7
    Join Date
    Sep 2010
    Posts
    3

    Thumbs down Never a Second Chance for First Impression

    OK, I beg your pardon, oh all-sapient mr. Anacedent. I meant DML-SQL, not T-SQL. I'm not an Oracle pro, you see, that's why I've been searching the web for a few answers. Thank you so very much for taking your time and your so-helpful words.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I meant DML-SQL, not T-SQL. I'm not an Oracle pro, you see
    OK, I beg your pardon, since I am not a minder reader and I accept at face value what is posted.

    The solution was posted.
    You are free to decline to use it or not.
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by TiagoRocha View Post
    So, I see there is just no way to do this in a T-SQL way..
    When you move from one DBMS to another you need to learn (and appreciate) new concepts.

    Things that work in Oracle won't work in MS SQL and vice versa.
    Every DBMS has a different approach to solve things.

    When you migrate you should not ask "how do I copy this solution to the other dbms?", but you need to find out "what is the best way to solve the (underlying) problem that my old solution solved?"

    This is especially true for any "script" written in a non-standard language (and T-SQL just as well as PL/SQL are proprietary for the respective DBMS)

    My example is the "Oracle way" of doing things like this. The basic concept is still the same as with MS SQL Server but the syntax is different.

Posting Permissions

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