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

--

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

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

--

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]

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

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]

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.

--

Malcolm,

> Muito obrigado.
De nada.

Do you speak Portuguese?

--
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]

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.


--