could do something like:
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#
)
RETURNING p_id INTO newly_created_id
(note the RETURNING syntax used in PL/SQL)
Outside of that, if you must stay inline, you could use
unique_poll_Num_s.CURRVAL. That should return the current value of the
new primary key. Although this is dangerous, make sure to keep your
transactions tight.
-Adam
----- Original Message -----
From: Daniel Kessler <[EMAIL PROTECTED]>
Date: Thu, 16 Sep 2004 09:56:46 -0400
Subject: Using a value from one insert for another insert
To: CF-Talk <[EMAIL PROTECTED]>
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]
