Users being users (me included) its possible that multiple instances of something (company, site, contact) can be created and end up in the database. I'm working on forms to merge the duplicates together. One table gives me grief


CREATE TABLE "Calls"
(
"_CallID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_fkProjects" INTEGER,
"_fkContacts" INTEGER,
"_fkCompanies" INTEGER,
"_fkSites" INTEGER,
....
....
CONSTRAINT "PK" PRIMARY KEY ("_CallID"),
CONSTRAINT "KeepUnique" UNIQUE ("_fkProjects", "_fkContacts", "_fkCompanies", "_fkSites")
)

with its unique constraint. I've come up with this bit of SQL to remove duplicates before they happen (otherwise the script bombs). It looks simple enough but its done my head in getting this far.


DELETE FROM Calls WHERE _CallID IN
(
SELECT _CallID FROM Calls WHERE _fkSites = 1574 AND _fkProjects IN
(
SELECT _fkProjects FROM Calls
WHERE
_fkSites IN (1574, 3221)
GROUP BY _fkProjects,_fkContacts, _fkCompanies
HAVING COUNT(_fkProjects) >1
)
)

Can anyone spot a mistake? Or an unintended consequence? Its only meant to remove the duplicate that WOULD be created by changing _fkSites.

Roy Lambert