Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
Convert DBISAM statement to ElevateDB |
Fri, Sep 4 2009 1:41 PM | Permanent Link |
"Malcolm" | Converting this one has got me stumped so I hope Tim or a 'Team'
member can ease my pain. Working DBISAM statement: DELETE FROM "Dives" AS d INNER JOIN "DiveSheets" AS s ON (d."Meet" = s."Meet" AND d."Event"=s."Event" AND d."DiverA"=s."DiverA" AND d."Round"=s."Round"); It should delete any records from "Dives" where the 4 column values match a record in "DiveSheets". I can't see how to construct a 'filter' to replace the join. Any ideas, or alternative constructs? Malcolm -- |
Fri, Sep 4 2009 1:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
<< Converting this one has got me stumped so I hope Tim or a 'Team' member can ease my pain. >> What are the primary keys for both tables ? -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 4 2009 2:20 PM | Permanent Link |
"Malcolm" | Tim Young [Elevate Software] wrote:
> Malcolm, > > << Converting this one has got me stumped so I hope Tim or a 'Team' > member can ease my pain. >> > > What are the primary keys for both tables ? Umm.. DiveSheets has a constraint 'PrimaryKey' with those 4 columns in that order plus one more. Dives is a Temporary table with a single index: CREATE INDEX "Primary" ON "Dives" ("Meet", "Event", "StartOrder", "Round") Malcolm -- |
Fri, Sep 4 2009 5:57 PM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
Try this: DELETE FROM "Dives" AS D WHERE EXISTS( SELECT * FROM "DiveSheets" WHERE "Meet" = D."Meet" AND "Event" = D."Event" AND "DiverA"= D."DiverA" AND "Round" = D."Round" ) -- Fernando Dias [Team Elevate] |
Fri, Sep 4 2009 7:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
<< Umm.. DiveSheets has a constraint 'PrimaryKey' with those 4 columns in that order plus one more. Dives is a Temporary table with a single index: CREATE INDEX "Primary" ON "Dives" ("Meet", "Event", "StartOrder", "Round") >> I was going to suggest a DELETE statement that uses multiple correlated sub-queries that link up the two tables by their primary keys, but Fernando's version is even better, so never mind. I'm not sure if my version would have worked correctly either. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 4 2009 7:58 PM | Permanent Link |
Fernando Dias Team Elevate | Tim,
I have no idea if the tables involved are big or small, but in general, can this kind of statements execute faster if we select only one column, for example SELECT "Meet") instead of SELECT * in the sub-query? Can it make any noticeable difference inside the EXISTS( ) ? -- Fernando Dias [Team Elevate] |
Sat, Sep 5 2009 4:24 AM | Permanent Link |
"Malcolm" | Fernando Dias wrote:
> Malcolm, > > Try this: > > DELETE FROM > "Dives" AS D > WHERE > EXISTS( > SELECT > * > FROM > "DiveSheets" > WHERE > "Meet" = D."Meet" AND > "Event" = D."Event" AND > "DiverA"= D."DiverA" AND > "Round" = D."Round" > ) It works, Fernando! Well done. After all the ng 'complaints' about the loss of 'EXISTS' for things like CREATE/DROP .. , I never even looked for WHERE EXISTS. Muito obrigado. -- |
Sat, Sep 5 2009 7:19 AM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
> Muito obrigado. De nada. Do you speak Portuguese? -- Fernando Dias [Team Elevate] |
Sat, Sep 5 2009 7:29 AM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
> After all the ng 'complaints' about the loss of 'EXISTS' for things > like CREATE/DROP .. , I never even looked for WHERE EXISTS. They are completely different things: You are talking about the DBISAM non-standard IF EXISTS and IF NOT EXISTS clauses, used in DDL statements; this one is the standard SQL predicate EXISTS, used to test if a subquery is empty or not. -- Fernando Dias [Team Elevate] |
Sat, Sep 5 2009 10:23 AM | Permanent Link |
"Malcolm" | Fernando Dias wrote:
> Malcolm, > > > Muito obrigado. > De nada. > > Do you speak Portuguese? No, but I was so pleased with your solution that I made the effort. -- |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |