Delet query with joined tables

[Frank List]

Hi,

I've run into this problem many times and have not found a good
solution yet.

Here's what I have:

table ParentTable - the "1" table
table ChildTable- the "many" table
ParentTable inner joined to ChildTable on ParentTable.ParentID =
ChildTable.ParentID_f

Here's what I would like to do but can't:

delete ChildTable.* from ChildTable inner join ParentTable on
ChildTable.ParentID_f = ParentTable.ParentID where
ParentTable.SomeField = 'something';

There are too many records in the ParentTable to make the following
practical, i.e. its VERY slow, so I'd like to avoid it (or is this
actually the best way?):

delete ChildTable.* from ChildTable where ChildTable.ParentID_f in
(Select ParentID from ParentTable where SomeField = 'something');

I'm interested in hearing how others handle this issue.

[Rich P]

If you have a one to many relationship between the Parent and child
table then you can create a relationship between the 2 tables in the
relationship window (which is on the menu bar when you are in the Tables
tab). In the relationship you specify Cascade Delete. When you delete
a row in the parent table all of the corresponding children rows will
also get deleted. Make sure you have a backup of your db first before
you delete the rows incase you delete the wrong rows.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Frank List]

Rich P wrote:

If you have a one to many relationship between the Parent and child
table then you can create a relationship between the 2 tables in the
relationship window (which is on the menu bar when you are in the Tables
tab). In the relationship you specify Cascade Delete. When you delete
a row in the parent table all of the corresponding children rows will
also get deleted. Make sure you have a backup of your db first before
you delete the rows incase you delete the wrong rows.

Thats true, but in the case I described above, I only want to delete
from the child table. I generally need to do this only in design and
development testing, but I can think of a case where - in production -
you may want to delete from the child only: if you have a large volume
of transaction or event-logging type data that looses its functional
utility over time for whatever reason, it would be advantageous to
archive it periodically based on criteria not contained entirely within
the child table. This is what I'm trying to figure out how to do. In
the past I've used code, but I'm wondering if there's some way to do
this using only SQL without using "in (...)".

[MarcHG]

Frank,

I tested this syntax on a case where some 400 rows are deleted out of a
joined table with about 10.000 rows and Access deleted the rows within
a second on my laptop. It is possible the operation could be slow
because of a failing index on at least Childtable.ParentID_f. A key on
ParentTable.Somefield also could help. Do you make use of referential
integrity ? I suppose not because it automatically will create useful
indexes. Performance also can suffer by a lot of reasons (heterogenous
join with another database-brand, Network-performance, locking by
multiple users or many thousands of records in a huge access-database).
In my experience almost always lack of indexes is the main
performance-killer. In my experience modifying thousand of records in
an table with more than a million records can be done within ten
minutes using proper SQL by a selfmade tool that analyses all records
and fields through VBA comparing with another table of a more than a
million records. Both your SQL-solutions will work, but I think the
first is in most cases probably the most efficient. When created with
the Query-editor using QBE and specifying a delete-query the
somefield-field is also listed but actually not deleted because only
all fields of a table (one or more rows) can be deleted and not just
one field. Access just doesn't show the exclusion-checkbox because the
delete-statement will only delete table-rows indicated with the
"table.*" identifier.

Marc

Frank List schreef:

Hi,

I've run into this problem many times and have not found a good
solution yet.

Here's what I have:

table ParentTable - the "1" table
table ChildTable- the "many" table
ParentTable inner joined to ChildTable on ParentTable.ParentID =
ChildTable.ParentID_f

Here's what I would like to do but can't:

delete ChildTable.* from ChildTable inner join ParentTable on
ChildTable.ParentID_f = ParentTable.ParentID where
ParentTable.SomeField = 'something';

There are too many records in the ParentTable to make the following
practical, i.e. its VERY slow, so I'd like to avoid it (or is this
actually the best way?):

delete ChildTable.* from ChildTable where ChildTable.ParentID_f in
(Select ParentID from ParentTable where SomeField = 'something');

I'm interested in hearing how others handle this issue.

[Frank List]

MarcHG wrote:

Frank,

I tested this syntax on a case where some 400 rows are deleted out of a
joined table with about 10.000 rows and Access deleted the rows within
a second on my laptop. It is possible the operation could be slow
because of a failing index on at least Childtable.ParentID_f. A key on
ParentTable.Somefield also could help. Do you make use of referential
integrity ? I suppose not because it automatically will create useful
indexes. Performance also can suffer by a lot of reasons (heterogenous
join with another database-brand, Network-performance, locking by
multiple users or many thousands of records in a huge access-database).
In my experience almost always lack of indexes is the main
performance-killer. In my experience modifying thousand of records in
an table with more than a million records can be done within ten
minutes using proper SQL by a selfmade tool that analyses all records
and fields through VBA comparing with another table of a more than a
million records. Both your SQL-solutions will work, but I think the
first is in most cases probably the most efficient. When created with
the Query-editor using QBE and specifying a delete-query the
somefield-field is also listed but actually not deleted because only
all fields of a table (one or more rows) can be deleted and not just
one field. Access just doesn't show the exclusion-checkbox because the
delete-statement will only delete table-rows indicated with the
"table.*" identifier.

Marc

Well here's what lead to my post in the first place, since you
mentioned the case of deleting from tables with millions of records-
thats exactly what I'm doing. I'm stress testing a new SQL Server
database schema where the "child" table could eventually have 10
million+ records and the "parent" table may reach into the upper 6
figures. Eventually I'll want to archive the "child" table data, but
not the "parent" table data, so I will probably be doing occasional
massive deletes as SOP. I might try your approach using VBA. Or I
might do a strategic denormalization and store all of the deletion
criteria data within the "child" table itself- I'll have to think about
that one.