Delete Query

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

Guest

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
 
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,

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
Click to expand...

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]
 
Back
Top