![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » Search Forums » Search Results » View Thread |
Messages 1 to 1 of 1 total |
![]() |
Sun, Feb 21 2010 9:23 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
This web page was last updated on Saturday, June 22, 2024 at 05:51 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |