Skip to main content
L
Luke10253
Inspiring
February 6, 2009
Answered

How do I insert record in DB ONLY if it does not already exist?

  • February 6, 2009
  • 2 replies
  • 415 views
I'm using Microsoft Access and I have 3 tables: Jobs, KSAs, and JobKSAs. JobKSAs is a linking table, i.e., it links the KSAs that are relevant to a given job.

A KSA can be linked to more than one job, but it should be linked to a single job only once. (i.e., there's no reason why the KSA "communication skill" should be linked to the job "salesperson" more than once.)

Unfortunately, I discovered that I could link a KSA to a given job as many times as I wanted, i.e., a new record would be added to Table: JobKSAs.

The KSA-Job link is created (or edited) using a form in a file entitled "add_ksa.cfm".

Table: JobKSAs contains these fields:
JKID (the primary key for this table),
frn_JobID (the foreign key pertaining to the unique ID of the given Job), and,
frn_KSAID (the foreign key pertaining to the unique ID of the given KSA).

The form data is transmitted to "ksa_au_pro.cfm", which I hope will run a check to see if a record in JobKSAs with the given frn_JobID and frn_KSAID already exists. If such a record already exists, then I don't want a new record added to Table: JobKSAs. In addition, I want the user to be taken to "ksa_already_linked.cfm", which tells the user that the KSA has already been linked to the given Job.

If a record in Table: JobKSAs containing frn_JobID and frn_KSAID does not already exist, then I want it to be added to Table: JobKSAs, and for the user to be taken to "ksa_confirm.cfm", which tells the user that the KSA has been linked to the given job.

Below is my code for "ksa_au_pro.cfm".

Thank you for any help with this!

Luke
    This topic has been closed for replies.
    Correct answer Luke10253
    I think I answered my own question. The code below appears to be working:


    <CFIF IsDefined("FORM.JKID")>

    <CFUPDATE DATASOURCE="ABC" TABLENAME="JobKSAs">

    <CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

    <CFELSE>

    <!--- Check for the record first. Want to see if the KSA has already been linked to this Job. --->
    <cfquery name="KSACheck" datasource="ABC">
    SELECT *
    FROM JobKSAs
    WHERE frn_JobID = #form.frn_JobID#
    AND frn_KSAID = #form.frn_KSAID#
    </cfquery>

    <!--- If the KSA has NOT alreay been linked, then add the linkage --->
    <cfif KSACheck.recordcount EQ 0>

    <CFINSERT DATASOURCE="ABC" TABLENAME="JobKSAs">

    <CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

    <cfelse>

    <!--- If the KSA HAS alreay been linked, then do not add to DB and inform user --->

    <CFLOCATION URL="ksa_already_linked.cfm?JobID=#JobID#">

    </cfif>

    </CFIF>

    2 replies

    D
    Dan_Bracuk
    Inspiring
    February 7, 2009
    Here is another way to approach it. Using your topic title, How do I insert record in DB ONLY if it does not already exist?

    insert into atable
    (field1, field2, etc)
    select distinct value1, value2 etc
    from some_small_table
    where
    (select count(*)
    from atable
    where field1 = value1
    and field2 = value2
    etc
    ) = 0
    L
    Luke10253AuthorCorrect answer
    Inspiring
    February 6, 2009
    I think I answered my own question. The code below appears to be working:


    <CFIF IsDefined("FORM.JKID")>

    <CFUPDATE DATASOURCE="ABC" TABLENAME="JobKSAs">

    <CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

    <CFELSE>

    <!--- Check for the record first. Want to see if the KSA has already been linked to this Job. --->
    <cfquery name="KSACheck" datasource="ABC">
    SELECT *
    FROM JobKSAs
    WHERE frn_JobID = #form.frn_JobID#
    AND frn_KSAID = #form.frn_KSAID#
    </cfquery>

    <!--- If the KSA has NOT alreay been linked, then add the linkage --->
    <cfif KSACheck.recordcount EQ 0>

    <CFINSERT DATASOURCE="ABC" TABLENAME="JobKSAs">

    <CFLOCATION URL="ksa_confirm.cfm?JobID=#JobID#">

    <cfelse>

    <!--- If the KSA HAS alreay been linked, then do not add to DB and inform user --->

    <CFLOCATION URL="ksa_already_linked.cfm?JobID=#JobID#">

    </cfif>

    </CFIF>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices