Delete Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 17 Nov 2005 20:20 GMT

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

Signature

Bob

Reply to this Message

Michel Walsh - 17 Nov 2005 21:00 GMT

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

> Hello all,
>
[quoted text clipped - 17 lines]
>
> Thank you

Reply to this Message

John Vinson - 17 Nov 2005 21:23 GMT

>Hello all,
>
[quoted text clipped - 15 lines]
>
>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]

Reply to this Message