Very Computer

by **Daniel Lawrenc** » Fri, 16 Nov 2001 03:19:21

On further review your suggestion does seem to be the fastest and easiest
way to accomplish what I need to do.

I was wondering if perhaps you could shed further light on your answer.

I have a few questions if you could please spare a few moments.

In my application I have two tables Orders and Details (both of which are
Paradox if it matters) which are linked on secondary indexes by a field
called OrderID. I have tried to code what you suggested below and for the
life of me can't as I always get a error to the effect that I am using a
keyword.

If at all possible can you please shed some light on how this would be done?

> The following query deletes all records in a detail table that have no
> corresponding record in a master table:

> delete detail.*
> from detail left join master
> on detail.key = master.key
> where master.key is null

> > Hey All,
> > I am working on a Paradox database, and need help to remove the records
> from
> > a child database when the master database is emptied or the record is
> > deleted.

> > The records being deleted are cancelled orders, quotes, invoices, and
> other
> > such transactions. I am easily able to get the record from the master
> table
> > but need to know how to get the sometimes multiple records from the
child
> > database. any ideas?

by **curio** » Fri, 16 Nov 2001 15:30:17

try this (assuming pkey - primary key in master and Fkey - foregn key in detail)

delete from detail where Fkey in
(select Pkey from master where CONDITION_DESIRED)

by **Daniel Lawrenc** » Sat, 17 Nov 2001 03:28:19

thanks for the input, but I was really hoping for some help on the previous
suggestion I was offered as from what I can see its pretty simple but I just
don't understand it. Unfortunately I am a newbie at Delphi database
programming so excuse the stupid questions and comments.

Quote:> try this (assuming pkey - primary key in master and Fkey - foregn key in
detail)

> delete from detail where Fkey in
> (select Pkey from master where CONDITION_DESIRED)

by **M.H. Avegaar** » Sat, 17 Nov 2001 05:38:59

Sorry, I just changed employer, so I haven't been answering posts lately.
Here is my answer to your previous question:

Put a TQuery on the form, set the DatabaseName property to the same value as
your TTable objects and use the following text in the SQL property:

delete Details.*
from Details left join Orders
on Details.OrderID = Orders.OrderID
where Orders.OrderID is null

and call Query1.ExecSQL in your code.

> On further review your suggestion does seem to be the fastest and easiest
> way to accomplish what I need to do.

> I was wondering if perhaps you could shed further light on your answer.

> I have a few questions if you could please spare a few moments.

> In my application I have two tables Orders and Details (both of which are
> Paradox if it matters) which are linked on secondary indexes by a field
> called OrderID. I have tried to code what you suggested below and for the
> life of me can't as I always get a error to the effect that I am using a
> keyword.

> If at all possible can you please shed some light on how this would be
done?

> > The following query deletes all records in a detail table that have no
> > corresponding record in a master table:

> > delete detail.*
> > from detail left join master
> > on detail.key = master.key
> > where master.key is null

> > > Hey All,
> > > I am working on a Paradox database, and need help to remove the
records
> > from
> > > a child database when the master database is emptied or the record is
> > > deleted.

> > > The records being deleted are cancelled orders, quotes, invoices, and
> > other
> > > such transactions. I am easily able to get the record from the master
> > table
> > > but need to know how to get the sometimes multiple records from the
> child
> > > database. any ideas?

by **Daniel Lawrenc** » Sat, 17 Nov 2001 10:50:41

Thanks for the updated info, I will let you know how I make out.

> Sorry, I just changed employer, so I haven't been answering posts lately.
> Here is my answer to your previous question:

> Put a TQuery on the form, set the DatabaseName property to the same value
as
> your TTable objects and use the following text in the SQL property:

> delete Details.*
> from Details left join Orders
> on Details.OrderID = Orders.OrderID
> where Orders.OrderID is null

> and call Query1.ExecSQL in your code.

> > On further review your suggestion does seem to be the fastest and
easiest
> > way to accomplish what I need to do.

> > I was wondering if perhaps you could shed further light on your answer.

> > I have a few questions if you could please spare a few moments.

> > In my application I have two tables Orders and Details (both of which
are
> > Paradox if it matters) which are linked on secondary indexes by a field
> > called OrderID. I have tried to code what you suggested below and for
the
> > life of me can't as I always get a error to the effect that I am using a
> > keyword.

> > If at all possible can you please shed some light on how this would be
> done?

> > > The following query deletes all records in a detail table that have no
> > > corresponding record in a master table:

> > > delete detail.*
> > > from detail left join master
> > > on detail.key = master.key
> > > where master.key is null

> > > > Hey All,
> > > > I am working on a Paradox database, and need help to remove the
> records
> > > from
> > > > a child database when the master database is emptied or the record
is
> > > > deleted.

> > > > The records being deleted are cancelled orders, quotes, invoices,
and
> > > other
> > > > such transactions. I am easily able to get the record from the
master
> > > table
> > > > but need to know how to get the sometimes multiple records from the
> > child
> > > > database. any ideas?

by **Daniel Lawrenc** » Sat, 17 Nov 2001 12:10:27

I tried what you suggested and I get a error
"invalid use of keyword.
Token: Details.*
from
LineNumber:1"

> Thanks for the updated info, I will let you know how I make out.

> > Sorry, I just changed employer, so I haven't been answering posts
lately.
> > Here is my answer to your previous question:

> > Put a TQuery on the form, set the DatabaseName property to the same
value
> as
> > your TTable objects and use the following text in the SQL property:

> > delete Details.*
> > from Details left join Orders
> > on Details.OrderID = Orders.OrderID
> > where Orders.OrderID is null

> > and call Query1.ExecSQL in your code.

> > > On further review your suggestion does seem to be the fastest and
> easiest
> > > way to accomplish what I need to do.

> > > I was wondering if perhaps you could shed further light on your
answer.

> > > I have a few questions if you could please spare a few moments.

> > > In my application I have two tables Orders and Details (both of which
> are
> > > Paradox if it matters) which are linked on secondary indexes by a
field
> > > called OrderID. I have tried to code what you suggested below and for
> the
> > > life of me can't as I always get a error to the effect that I am using
a
> > > keyword.

> > > If at all possible can you please shed some light on how this would be
> > done?

> > > > The following query deletes all records in a detail table that have
no
> > > > corresponding record in a master table:

> > > > delete detail.*
> > > > from detail left join master
> > > > on detail.key = master.key
> > > > where master.key is null

> > > > > Hey All,
> > > > > I am working on a Paradox database, and need help to remove the
> > records
> > > > from
> > > > > a child database when the master database is emptied or the record
> is
> > > > > deleted.

> > > > > The records being deleted are cancelled orders, quotes, invoices,
> and
> > > > other
> > > > > such transactions. I am easily able to get the record from the
> master
> > > > table
> > > > > but need to know how to get the sometimes multiple records from
the
> > > child
> > > > > database. any ideas?

by **M.H. Avegaar** » Sun, 18 Nov 2001 04:25:50

What are the exact names of your tables and fields ?

It could be that you have to change it to something like:

delete D.*
from "Details.db" D left join "Orders.db" O
on D."OrderID" = O."OrderID"
where O."OrderID" is null

> I tried what you suggested and I get a error
> "invalid use of keyword.
> Token: Details.*
> from
> LineNumber:1"

> > Thanks for the updated info, I will let you know how I make out.

> > > Sorry, I just changed employer, so I haven't been answering posts
> lately.
> > > Here is my answer to your previous question:

> > > Put a TQuery on the form, set the DatabaseName property to the same
> value
> > as
> > > your TTable objects and use the following text in the SQL property:

> > > delete Details.*
> > > from Details left join Orders
> > > on Details.OrderID = Orders.OrderID
> > > where Orders.OrderID is null

> > > and call Query1.ExecSQL in your code.

> > > > On further review your suggestion does seem to be the fastest and
> > easiest
> > > > way to accomplish what I need to do.

> > > > I was wondering if perhaps you could shed further light on your
> answer.

> > > > I have a few questions if you could please spare a few moments.

> > > > In my application I have two tables Orders and Details (both of
which
> > are
> > > > Paradox if it matters) which are linked on secondary indexes by a
> field
> > > > called OrderID. I have tried to code what you suggested below and
for
> > the
> > > > life of me can't as I always get a error to the effect that I am
using
> a
> > > > keyword.

> > > > If at all possible can you please shed some light on how this would
be
> > > done?

> > > > > The following query deletes all records in a detail table that
have
> no
> > > > > corresponding record in a master table:

> > > > > delete detail.*
> > > > > from detail left join master
> > > > > on detail.key = master.key
> > > > > where master.key is null

> > > > > > Hey All,
> > > > > > I am working on a Paradox database, and need help to remove the
> > > records
> > > > > from
> > > > > > a child database when the master database is emptied or the
record
> > is
> > > > > > deleted.

> > > > > > The records being deleted are cancelled orders, quotes,
invoices,
> > and
> > > > > other
> > > > > > such transactions. I am easily able to get the record from the
> > master
> > > > > table
> > > > > > but need to know how to get the sometimes multiple records from
> the
> > > > child
> > > > > > database. any ideas?

by **Daniel Lawrenc** » Sun, 18 Nov 2001 06:03:45

Thanks for the input. The exact names of the tables are details and orders
(both are Paradox tables). The link between the two is called OrderID, in
the Orders table OrderID is the primary and on the Details table OrderID is
a secondary index.

> What are the exact names of your tables and fields ?

> It could be that you have to change it to something like:

> delete D.*
> from "Details.db" D left join "Orders.db" O
> on D."OrderID" = O."OrderID"
> where O."OrderID" is null

> > I tried what you suggested and I get a error
> > "invalid use of keyword.
> > Token: Details.*
> > from
> > LineNumber:1"

> > > Thanks for the updated info, I will let you know how I make out.

> > > > Sorry, I just changed employer, so I haven't been answering posts
> > lately.
> > > > Here is my answer to your previous question:

> > > > Put a TQuery on the form, set the DatabaseName property to the same
> > value
> > > as
> > > > your TTable objects and use the following text in the SQL property:

> > > > delete Details.*
> > > > from Details left join Orders
> > > > on Details.OrderID = Orders.OrderID
> > > > where Orders.OrderID is null

> > > > and call Query1.ExecSQL in your code.

> > > > > On further review your suggestion does seem to be the fastest and
> > > easiest
> > > > > way to accomplish what I need to do.

> > > > > I was wondering if perhaps you could shed further light on your
> > answer.

> > > > > I have a few questions if you could please spare a few moments.

> > > > > In my application I have two tables Orders and Details (both of
> which
> > > are
> > > > > Paradox if it matters) which are linked on secondary indexes by a
> > field
> > > > > called OrderID. I have tried to code what you suggested below and
> for
> > > the
> > > > > life of me can't as I always get a error to the effect that I am
> using
> > a
> > > > > keyword.

> > > > > If at all possible can you please shed some light on how this
would
> be
> > > > done?

> > > > > > The following query deletes all records in a detail table that
> have
> > no
> > > > > > corresponding record in a master table:

> > > > > > delete detail.*
> > > > > > from detail left join master
> > > > > > on detail.key = master.key
> > > > > > where master.key is null

> > > > > > > Hey All,
> > > > > > > I am working on a Paradox database, and need help to remove
the
> > > > records
> > > > > > from
> > > > > > > a child database when the master database is emptied or the
> record
> > > is
> > > > > > > deleted.

> > > > > > > The records being deleted are cancelled orders, quotes,
> invoices,
> > > and
> > > > > > other
> > > > > > > such transactions. I am easily able to get the record from the
> > > master
> > > > > > table
> > > > > > > but need to know how to get the sometimes multiple records
from
> > the
> > > > > child
> > > > > > > database. any ideas?

by **Daniel Lawrenc** » Sun, 18 Nov 2001 06:05:59

Unfortunately that also gives the same error