Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: update statement

    hi

    i m having 35 fields in a table like t_answer with answer1 to answer35

    i m having one table name m_answer with answer column

    i have to insert only fields from t_answer which are not null into m_answer.

    i have to check each and every field which is not null and insert into m_answer

    tat means 35 insert statements

    can u have any other alternative to insert into table whichever fields are not null ????

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could use dynamic PL/SQL to construct the inserts in a loop:
    Code:
    declare
       l_sql varchar2(200);
    begin
       for i in 1..35 loop
          l_sql := 'insert into m_answer(answer) select answer' || i 
            || ' from t_answer where answer' || i || ' is not null';
          execute immediate l_sql;
       end loop;
    end;
    But really, you have a very poor table design for t_answer, which is the root of your problem.

Posting Permissions

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