Delete Query

[Bob]

Hello all,

I have a table that has unique records. I received a file from users to
delete some records. How can I do this? I don't want to type the record Id on
the Query criteria, because there are hundred of records. I want to match
both files by record Id and if there is a match then delete all the matched
records.
e.g

File1 File 2 from the user
RecID Rec ID

123 123
456 456
789

I want to delete 123,456 by matching these 2 files.

Thank you

 

[Michel Walsh]

Hi,


DELETE myTable.*
FROM myTable
WHERE id IN( SELECT a.id FROM otherTable As a)


or

DELETE DISTINCTROW myTable.*
FROM myTable INNER JOIN otherTable ON myTable.id=otherTable.id



Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

Hello all,

I have a table that has unique records. I received a file from users to
delete some records. How can I do this? I don't want to type the record Id on
the Query criteria, because there are hundred of records. I want to match
both files by record Id and if there is a match then delete all the matched
records.
e.g

File1 File 2 from the user
RecID Rec ID

123 123
456 456
789

I want to delete 123,456 by matching these 2 files.

Thank you

Create a Query joining File1 with File2 by RecID (which should be the
Primary Key of both tables, set it as such if it isn't).

Select RecID from File1 as the only field.

Change the query to a Delete query and run it.

The SQL would be

DELETE File1.RecID FROM File1 INNER JOIN File2 ON File1.RecID =
File2.RecID;


John W. Vinson[MVP]