Using a value from one insert for another insert

[Daniel Kessler]

I'm in Oracle.  I do an insert for a poll question, then I need to
get the ID of that question and insert it into each one of the
answers that I write to a different table (see code below).  Without
a separate query, is there any way to get the ID inside the same
query to use again for the answers?  Also, would doing a seperate
query be a bad idea?

thanks.

<CFQUERY NAME="addItem" DATASOURCE="eatsmart">
          INSERT INTO fsnep_polls
             (
             p_id,p_date_added,p_question,
             p_status,p_date_last_used
             )
          VALUES
             (
             unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#',
             #Form.p_status#,#p_date_last_used#
             )

             <cfloop index="ii" from="1" to="5">
                 <cfif Form.p_answer1 NEQ "">
                     INSERT INTO fsnep_pollAnswers
                         (
                         pA_ID,pA_pollID,pA_answer
                         )
                      VALUES
                         (
                         unique_pollAnswers_Num_s.NEXTVAL,#Form.p_id#,
                         '#Form.p_answer1#'
                         )
                 </cfif>
                 break;
               </cfloop>
</CFQUERY>

--
Daniel Kessler

Department of Public and Community Health
University of Maryland
Suite 2387 Valley Drive
College Park, MD  20742-2611
301-405-2545 Phone
www.phi.umd.edu

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

---