5 Replies Latest reply on May 8, 2006 7:02 PM by bah1234ir

    Prevent Duplicate Insertion of Record

    flooker Level 1
      I am trying to acheive some thing like this but i beleive Insert Statement is not allowed in Case or Select is expecting some thing in return that is why it keeps on giving me Missing Expression . Is there any other way to prevent duplicate insertion . i know we can do it by running another Query and then based on recoundcount or count of that seperate query inserting records but that i dont want to do .


      SELECT CASE
      WHEN ( SELECT COUNT(*) FROM THACARA WHERE ORG_CNY_CD = 'US' AND ORG_REG_NR = '02' AND ORG_DIS_NR = '51') > 0
      THEN (' DUPLICATE EXSIST')
      ELSE (INSERT INTO THACARA ( USR_NR, ORG_FAC_LOC_NR, )
      values ( 'Yousaf', 'FLIMI'))
      END AS TEST FROM DUAL;

      any ideas ??
        • 1. Prevent Duplicate Insertion of Record
          Dan Bracuk Level 5
          Each sql query can only be one type. It can be one of select, update, delete, or insert.

          What you might want to do is to start with a select query. The recordcount variable will tell you if the record is there. Then you use cfif/cfelse to separate your code for your insert query from whatever you want to do if the record is already there.

          Or, you can use a subquery, something like

          insert into mytable
          select fields from dual
          where not exists
          (select fields from mytable)

          The latter may or may not work. I've never tried it.
          • 2. Re: Prevent Duplicate Insertion of Record
            Qohelet
            Since you refer to DUAL I am assuming your database is Oracle and DUAL is a 1 row table.
            • 3. Prevent Duplicate Insertion of Record
              flooker Level 1
              Dan Your answer is perfect also . ..

              insert into mytable
              select fields from dual
              where not exists
              (select fields from mytable)

              This Worked too .. thanks a lot Guys ..
              • 4. Re: Prevent Duplicate Insertion of Record
                flooker Level 1
                what if i have 10 records to insert in database, instead of using Insert within CFLOOP or using stored procs is any way to do it ? let say i have a comma delimited list of all the 10 records then within one insert statement can i add them? is there any possible way to bypass 10 insert repititons ?

                • 5. Re: Prevent Duplicate Insertion of Record
                  bah1234ir Level 1
                  It would be a lot better for everyone if you actually put a unique constraint on your table rather than all this programming (remember Oracle can have constraints). and preferably handle it in a proper procedure and not injection - note that the "Answer" in this thread does not actually return any information about whether the record was inserted or not, just the actual record (nor does it update the non-duplicate columns).
                  BEGIN
                  insert stuff
                  return 'inserted'
                  when DUP_VAL_ON_INDEX
                  update other stuff not making up the unique columns
                  return 'duplicate'
                  END