Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Deduping a table |
Sat, Sep 20 2008 11:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I'm going to create a unique constraint on one of the tables I'm transferring to ElevateDB, problem is there are some duplicates. The important bits of the structure are
_ID: autoinc _Plan: varchar _Company: varchar _Contact: varchar I can get a list of the duplicated entries using select _ID, _plan,_company,_contact, Count(1) as fred INTO "Memory\Temp" from projectdetails group by _plan,_company,_contact having count(1) > 1; Mostly its 2 offs but there are a few 4 offs what I want to do next is delete the ones in each set with the lowest _ID. And here's where I'm stuck Roy Lambert |
Sat, Sep 20 2008 2:55 PM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:CD5D7330-C5F8-4C72-82D4-7D2CE330DCF8@news.elevatesoft.com... > I'm going to create a unique constraint on one of the tables I'm > transferring to ElevateDB, problem is there are some duplicates. The > important bits of the structure are > > _ID: autoinc > _Plan: varchar > _Company: varchar > _Contact: varchar > > I can get a list of the duplicated entries using > > select _ID, _plan,_company,_contact, Count(1) as fred INTO "Memory\Temp" > from projectdetails > group by _plan,_company,_contact > having count(1) > 1; > I'm not sure the above gets you the duplicates. I run this SQL on a small table called fred (don't ask) with a structure similar to yours, should do the trick. select a, b, sum(1) mysum into memory\temp from fred group by a, b having mysum > 1; select max(id) maxid, a, b into memory\temp2 from fred f join memory\temp t on (t.a = f.a) and (t.b = f.b) group by a, b; delete from fred f join memory\temp2 m on f.a = m.a and f.b = m.b where f.id <> m.maxid; Robert |
Sun, Sep 21 2008 4:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
>I'm not sure the above gets you the duplicates. I run this SQL on a small >table called fred (don't ask) with a structure similar to yours, should do >the trick. I don't need to ask - its one I use a lot just that in my case it never makes it through to production I also have lots of things called qaz. I'll try your code - thanks. Roy Lambert |
Sun, Sep 21 2008 12:11 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
Worked nicely - thanks again Roy Lambert |
Sun, Sep 21 2008 2:21 PM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:22A49E50-5B7E-4FFA-B971-84A63537B83D@news.elevatesoft.com... > Robert > > > Worked nicely - thanks again > Sure. This issue comes up periodically, is there some repository of solutions / tips where the answers can be stored? Sort of what Schkolnik has in his website? Robert |
Sun, Sep 21 2008 2:51 PM | Permanent Link |
"Malcolm" | Robert wrote:
> > Sure. This issue comes up periodically, is there some repository of solutions / tips where the answers can be stored? Sort of what Schkolnik has in his website? > Well, personally, I use Steve Forbes' Knowledge Base for storing the little bits of code that save my life. At one time there was talk of a co-operative effort to contribute items to be made available as a downloadable import - but I heard no more. Malcolm -- |
Mon, Sep 22 2008 8:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Robert,
<< Sure. This issue comes up periodically, is there some repository of solutions / tips where the answers can be stored? Sort of what Schkolnik has in his website? >> This just came up on the EDB newsgroups also. I'll see about adding something where everyone can add bits like this to a central repository. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Sep 22 2008 9:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>This just came up on the EDB newsgroups also. I'll see about adding >something where everyone can add bits like this to a central repository. Nice idea. I'd like to suggest appointing a couple of wardens as well who have the power to remove any tips that don't work. Roy Lambert |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |