Delete query for multiple records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I received a file consisting of 174 original records that we want to delete
from a table consisting of thousands of records. How can I write a query
that will match and delete each of the records without having to write a
separate query for each record?
 
Is there a single field in both tables that has matching data? If so you can
do it using a subquery something like this:

DELETE MainTable.*
FROM MainTable
WHERE MainTable.ID In (select ID from [174 original records]);

Before doing this make a complete backup of the tables involved or the
entire database.
 
Each record I received in my temp (to delete) table has an original Account
ID and Mail Code that has a match in the main table. I don't want to delete
all the Account ID's or Mail Codes that match the list I received, I only
want to delete the matching Account ID's and Mail Codes that have a date of
3/2/07. Does this help?

Jerry Whittle said:
Is there a single field in both tables that has matching data? If so you can
do it using a subquery something like this:

DELETE MainTable.*
FROM MainTable
WHERE MainTable.ID In (select ID from [174 original records]);

Before doing this make a complete backup of the tables involved or the
entire database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


IC Data said:
I received a file consisting of 174 original records that we want to delete
from a table consisting of thousands of records. How can I write a query
that will match and delete each of the records without having to write a
separate query for each record?
Click to expand...
Click to expand...
 
Since you need two fields to match between the tables, an EXISTS statement
will probably work better than IN. Again make a backup or two first.

DELETE
FROM ASA
WHERE Exists (SELECT *
FROM ASA_CHILD
WHERE ASA.AsaID = ASA_Child.AN_id
AND ASA.Month = ASA_Child.Month <> False)
AND ASA.DateField = #3/2/07# ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


IC Data said:
Each record I received in my temp (to delete) table has an original Account
ID and Mail Code that has a match in the main table. I don't want to delete
all the Account ID's or Mail Codes that match the list I received, I only
want to delete the matching Account ID's and Mail Codes that have a date of
3/2/07. Does this help?

Jerry Whittle said:
Is there a single field in both tables that has matching data? If so you can
do it using a subquery something like this:

DELETE MainTable.*
FROM MainTable
WHERE MainTable.ID In (select ID from [174 original records]);

Before doing this make a complete backup of the tables involved or the
entire database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


IC Data said:
I received a file consisting of 174 original records that we want to delete
from a table consisting of thousands of records. How can I write a query
that will match and delete each of the records without having to write a
separate query for each record?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top