CODEWITHSUNDEEP
sqlcoldfusionsybasesap-ase
espresso_coffee
espresso_coffee

Reputation: 6110

Check if record exists then insert new row in database table?

I'm trying to improve the old code that I'm working on. The current code can be simplified and use SQL to process the insert. Here is example:

<!--- Check if user exists in Table 1 ---> 
<cfquery name="checkUser" datasource="#application.datasource#">
    SELECT user_id 
    FROM table1
    WHERE user_id = '#session.user_id#'
</cfquery>

<cfif !checkUser.recordcount>
    <cfset password = functions.encryptPsw("form.pswd") />
    <cfquery name="getInfo" datasource="#datasource#">
        SELECT fname, lname
        FROM contact
        WHERE userid = #session.user_id#
    </cfquery>

    <cfquery name="insertUser" datasource="#application.database#">
        INSERT INTO inter_work_tbl (
            user_id, 
            first_name, 
            last_name, 
            password
        ) VALUES (
            '#session.user_id#', 
            '#trim(getInfo.fname)#', 
            '#trim(getInfo.lname)#', 
            '#password#'
        )
    </cfquery>
</cfif>

I'm wondering if this can be done with Sybase SQL and use IF NOT EXISTS then run Insert statement? If anyone have good example or suggestions please let me know. Thank you.

Upvotes: 1

Views: 1504

Answers (3)

user12031119
user12031119

Reputation: 1228

Sorry, I don't have a 50 reputation to comment, so I'm replying to this as an answer instead. Sounds like you can simply use the sql merge statement to accomplish this.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/commands89.htm

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Your question says that you want to improve your code. That being the case, let's start with this. You have:

<cfif !checkUser.recordcount>

That's not really readable or intuitive. Here is another way to express that condition:

<cfif checkUser.recordcount eq 0>

Next, in the unlikely event that you posted actual code, your checkUser query looks for records in table1 and if there are none, puts a record into a different table - inter_work_tbl. That seems a bit dicey.

Next, you get information from a table called contact and insert it into your inter_work_tbl table. Even if you think you will always have a record in the contact table, what if you don't? Your insert query will crash due to an undefined variable. Also, why do you need the name in table inter_work_tbl? You already have in the contact table. You are breaking normalization for no apparent reason.

As far as simplifying the code to use sql only, @markp's answer shows you how to do that.

Upvotes: 1

markp-fuso
markp-fuso

Reputation: 34698

I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...

  • if record does not exist in table1 but
  • record does exit in contact then
  • insert a row into inter_work_tbl

The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):

insert into inter_work_tbl

      (user_id
      ,first_name
      ,last_name
      ,password)

select '#session.user_id#',
       c.fname,
       c.lname,
       '#password#'

from   contact c

where  c.userid = #session.user_id#
and    not exists(select  1
                  from    table1 t
                  where   t.user_id = c.userid)

Upvotes: 3

Related Questions