cfquery update if data exists in second table, otherwise insert

armanbena
armanbena used Ask the Experts™
on
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.administer#',

                                                 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_timestamp">

);

end

</cfquery>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
well you can simply do this in sql server using simple query why cretaing a procedure!

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
<cfquery name="put" datasource="myDsn">
   SET NOCOUNT ON
      IF NOT EXISTS 
            (SELECT * FROM myTable
             WHERE   email = '#form.email#'
             AND ....)
         BEGIN
            INSERT INTO myTable
               (email, fnmae, lname, ...)
            VALUES
               ('#form.email#', 
                '#form.fname#',
                '#form.lname#', 
                ...)
            SELECT SCOPE_IDENTITY() AS newId
         END
      ELSE
         BEGIN
            UPDATE   myTable
            SET      fname = '#form.fname#',
                  lname = '#form.lname#',
            WHERE   email = '#form.Email#'
            
            SELECT id AS newid 
            FROM   myTable
            WHERE   email = '#form.email#'
      
         END   
   SET NOCOUNT OFF
   </cfquery>
   <!--- set a variable to the id returned --->
   <cfset usersId = put.newid

Open in new window

that did not do it, but thank you.

Author

Commented:
that was not sol. but am seeing screen that this is abandoned question.

Author

Commented:
that was not sol. but am seeing screen that this is abandoned question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial