armanbena
asked on
cfquery update if data exists in second table, otherwise insert
Hi all,
I am working on creating a cfquery to update data from a form if there is a record in a second table that already exists, otherwise insert. I think I'm thinking more in terms of sql server and creating a stored procedure, but do not know of proper syntax in setting up a cfquery on page.
This is what I have:
<cfquery name="qUpdateData" datasource="ybgmaster">
DECLARE @review_id int
DECLARE @presale_id int
select @review_id = #url.id#
from tbl_DNOA_Review_MA
where d_id = #url.id#
if @review_id = #url.id#
begin
UPDATE tbl_DNOA_Review_MA as a
set DNoA_Administer='#form.adm inister#',
DNoA_Comments='#form.DNoA_ comments#' ,
other_Reason='#form.other_ reason#'
where a.d_id=#url.id#;
else
begin
INSERT INTO tbl_DNOA_Review_MA
(
d_id,
DNoA_Administer,
DNoA_Comments,
other_Reason,
DNoA_Reviewer,
date_Reviewed)
VALUES
(
#url.id#,
'#form.administer#',
'#form.DNoA_comments#',
'#form.other_reason#',
'#form.DNoA_Reviewer#',
<cfqueryparam value="#form.date_Reviewed #" cfsqltype="cf_sql_timestam p">
);
end
</cfquery>
I am working on creating a cfquery to update data from a form if there is a record in a second table that already exists, otherwise insert. I think I'm thinking more in terms of sql server and creating a stored procedure, but do not know of proper syntax in setting up a cfquery on page.
This is what I have:
<cfquery name="qUpdateData" datasource="ybgmaster">
DECLARE @review_id int
DECLARE @presale_id int
select @review_id = #url.id#
from tbl_DNOA_Review_MA
where d_id = #url.id#
if @review_id = #url.id#
begin
UPDATE tbl_DNOA_Review_MA as a
set DNoA_Administer='#form.adm
DNoA_Comments='#form.DNoA_
other_Reason='#form.other_
where a.d_id=#url.id#;
else
begin
INSERT INTO tbl_DNOA_Review_MA
(
d_id,
DNoA_Administer,
DNoA_Comments,
other_Reason,
DNoA_Reviewer,
date_Reviewed)
VALUES
(
#url.id#,
'#form.administer#',
'#form.DNoA_comments#',
'#form.other_reason#',
'#form.DNoA_Reviewer#',
<cfqueryparam value="#form.date_Reviewed
);
end
</cfquery>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that was not sol. but am seeing screen that this is abandoned question.
ASKER
that was not sol. but am seeing screen that this is abandoned question.
Here is the thing you can do
or follow this blog entry:
http://www.coldfusionmuse.com/index.cfm/2006/6/23/insert.update.one.statement
Open in new window