How to check for duplicate records on multiple insert

[jedimatt]

Hi all,

I have a good one here and i am hoping there is an answer for this.

I have a form which displays a profile. Each profile has multiple rows of data, each row consists of a sort order, an operation name and a delete flag, which when saved runs through a loop in a cfc and inserts into the db, no problem there.

There is a business rule that the Operation name field must not be duplicated within each profile.

Now i can write code to check for a duplication of an operation when a new operation name is created, no problem.

What i cant seem to get my head around is to check to make sure that there are no duplicates in any of the rows.

What i first did was create a select statement to check for duplicates and then if the select statement returned a recordcount greater than 0 it would return back an error.

Of course this doesnt work because each form value on the screen already exists in the db so every value was handled as a duplicate.

The only way i can think of doing it is to write a cfc that deletes the existing values from the table and creates a new values and at the point of creation checks for duplicate values, if there are dups, then do a transaction roll back and inform the user, i know its a bit wild but looking for any other ideas on how to handle this.

JM

[jedimatt]

Managed to figure out how to do this.

If anyones interested then let me know.

[arrgh]

Care to share? ;-)

[jedimatt]

Yep, no problem:

I created the usual insert sql statement to insert all of the updated rows back into the DB.

Next i created a dupcheck select statement which basically is a select statement which checks for dups onitself:

<cfquery name="qryCheckOperationNameDups" datasource="UKCHAPP145">
	SELECT DISTINCT
		a.OPERATION_UUID
    FROM
		UKCHAPP145.TBL_OPERATION a,
        UKCHAPP145.TBL_OPERATION b
    WHERE 
		a.HEADER_UUID = <cfqueryparam value="#arguments.HEADER_UUID#" cfsqltype="cf_sql_integer">
    AND 
		b.HEADER_UUID = <cfqueryparam value="#arguments.HEADER_UUID#" cfsqltype="cf_sql_integer">
	AND
    	a.OPERATION_UUID > b.OPERATION_UUID
    AND
    	a.OPERATION_NAME = b.OPERATION_NAME
    AND
    	a.DELETE_FLAG = <cfqueryparam value="N" cfsqltype="cf_sql_varchar">
    AND
    	b.DELETE_FLAG = <cfqueryparam value="N" cfsqltype="cf_sql_varchar">
    ORDER BY
    	a.OPERATION_UUID
</cfquery>

Then used an if statement to run a condition to see if any rows were returned, if they were then return back to the user an error plus do a transaction rollback.

It works very well.
I found the dup select statement idea on a blog and modified the code to suit my application.

cheers

JM