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.Pare ntID_f. A key on
ParentTable.Som efield 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.Par entID =
ChildTable.Pare ntID_f
Here's what I would like to do but can't:
delete ChildTable.* from ChildTable inner join ParentTable on
ChildTable.Pare ntID_f = ParentTable.Par entID where
ParentTable.Som eField = '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.Pare ntID_f in
(Select ParentID from ParentTable where SomeField = 'something');
I'm interested in hearing how others handle this issue.