Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    4

    Unanswered: Alter a table add column if not exists

    hi ,

    this is prasath,

    i am new to db2,

    i am in need to ALTER TABLE ADD COLUMN IF THE COLUMN DOES NOT EXISTS<

    is it any solutions for this ?

    if any possible solutions please reply me

    Thanks and Regards

    Prasath A

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you try to add the column and it already exists, then the add will fail. If you try to add it and it does not already exists, it will succeed. You can do this one of two ways. You can check if the columns already exists by querying syscat.columns, or you just try to add the column and handle the error if it already exists.

    Andy

  3. #3
    Join Date
    May 2010
    Posts
    4

    Thank You ANDY

    CREATE PROCEDURE PR1
    BEGIN
    IF NOT EXISTS(
    select * from SYSIBM.syscolumns where tbname='S_2' and name='NAME' ;
    )THEN

    ALTER TABLE S_2 ADD S_2_FIRSTNAME VARCHAR(250);
    END IF;
    END

    actualy am using the above procedure to get do the stuff.

    but the procedure is not created shows some error,

    i have get the code to find the column is already exists or not

    but the procedure is not created ,

    can u please reply , what could be the issue

    thanks and regards

    Prasath A

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How would I know what the problem is? You did not supply the error you are getting.

    Andy

  5. #5
    Join Date
    May 2010
    Posts
    4

    hi andy

    can u able to give me the code for alter the table add a column if the does not exists,if exists dont add.

    thanks and regards

    Prasath a

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    First, you must include a schema name in your query or you may not check for the right table. Then you may have to use dynamic SQL instead of static SQL for the ALTER TABLE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    May 2010
    Posts
    4

    Thank you stolze

    Thank you stolze

Posting Permissions

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