Link to home
Start Free TrialLog in
Avatar of armanbena
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.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>
Avatar of Coast Line
Coast Line
Flag of Canada image

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

ASKER CERTIFIED SOLUTION
Avatar of armanbena
armanbena

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of armanbena
armanbena

ASKER

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