Prevent Duplicate Insertion of Record

[Guest]

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 ??

[Dan_Bracuk]

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.

[Qohelet · Accepted solution]

Since you refer to DUAL I am assuming your database is Oracle and DUAL is a 1 row table.

[Guest]

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 ..

[Guest]

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 ?

[Guest]

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