Link to home
Create AccountLog in
Avatar of bcrosby007
bcrosby007Flag for United States of America

asked on

Remove Duplicate entries in Access DB using Coldfusion

I run a query that writes data into a database. It usually adds around 500 rows to the database. Depending on the critera chosen by the end user, it could add about 15 duplicate entries as well (different id's, same other data).

Is there some code where after i write the data to the databse, it can scour the database for duplicates and remove them (while keeping 1)?

<cfloop list="#form.proj_type_sub#" index="subs" >

    <cfquery datasource="checklist" name="addchecklistitems">
    INSERT INTO tbl_project_items (ProjectID, Checklist, ChecklistItemID)
    SELECT "#form.id#", tbl_checklist_items.Checklist, tbl_checklist_items.ChecklistItemID
    FROM tbl_checklist_items
    WHERE NOT EXISTS tbl_checklist_items.ProjType LIKE "%#form.proj_type#%" AND tbl_checklist_items.ProjSUBType LIKE "%,#subs#,%" OR tbl_checklist_items.ProjSUBType LIKE '%27%'
    </cfquery>
</cfloop>

Open in new window

Avatar of Sudhindra A N
Sudhindra A N
Flag of India image

try this query in side the cfquery tag... after your insert tag.
DELETE
FROM tbl_project_items t
WHERE t.ProjectID IN
(SELECT F.ProjectID
FROM tbl_project_items AS F
WHERE Exists (SELECT Checklist, ChecklistItemID, Count(ID)
FROM tbl_project_items t1
WHERE t1.Checklist = F.Checklist
   AND t1.ChecklistItemID = F.ChecklistItemID
GROUP BY t1.Checklist, t1.ChecklistItemID
HAVING Count(t1.ID) > 1))
AND t.ID NOT IN
(SELECT Min(ProjectID)
FROM tbl_project_items AS F2
WHERE Exists (SELECT Checklist, ChecklistItemID, Count(ProjectID)
FROM tbl_project_items F3
WHERE F3.Checklist = F2.Checklist
   AND F3.ChecklistItemID = F2.ChecklistItemID
GROUP BY F2.Checklist, F2.ChecklistItemID
HAVING Count(F2.ProjectID) > 1)
GROUP BY Checklist, ChecklistItemID);

Open in new window

Avatar of bcrosby007

ASKER

Should i do this after the cfloop occurs?
I received this error when i tried running the statement.

You tried to execute a query that does not include the specified expression 'Checklist' as part of an aggregate function.
try this...

DELETE
FROM tbl_project_items t
WHERE t.ProjectID IN
(SELECT F.ProjectID
FROM tbl_project_items AS F
WHERE Exists (SELECT t1.Checklist, t1.ChecklistItemID, Count(t1.ProjectID)
FROM tbl_project_items t1
WHERE t1.Checklist = F.Checklist
   AND t1.ChecklistItemID = F.ChecklistItemID
GROUP BY t1.Checklist, t1.ChecklistItemID
HAVING Count(t1.ProjectID) > 1))
AND t.ProjectID NOT IN
(SELECT Min(F2.ProjectID)
FROM tbl_project_items AS F2
WHERE Exists (SELECT Checklist, ChecklistItemID, Count(ProjectID)
FROM tbl_project_items F3
WHERE F3.Checklist = F2.Checklist
   AND F3.ChecklistItemID = F2.ChecklistItemID
GROUP BY F2.Checklist, F2.ChecklistItemID
HAVING Count(F2.ProjectID) > 1)
GROUP BY F2.Checklist, F2.ChecklistItemID)

Open in new window



if you get the above specified (in your post) add the column name in to the "Group by" clause in your SQL.
Still getting the aggregate error.
Where do i put the column name?
SOLUTION
Avatar of Sudhindra A N
Sudhindra A N
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Same error.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks