delete table 2

[JCAA]

I know that this probably sounds like a very stupid question but I still need to ask as I don’t know the answer. I currently have a table with a lot of records I have another table with records that needs to be deleted from the first big table. How do I delete the records from the big table that are also in my smaller table, also not all fields are in the smaller table but it does have a common field to match up the records with. Thanks!

 

[JonFer]

Delete * from BigTable
Where CommonField Not In (Select Distinct CommonField
From SmallTable) ;

The CommonField should be a unique identifier for each record.

 

[Transcend]

Shouldn't it be

Delete * from BigTable
Where CommonField In (Select Distinct CommonField
From SmallTable)

I thought JCAA wanted to deleted all the records from the big table that DO exist in the smaller table?

Transcend

 

[JonFer]

You're right.

Did I mention that it is a good idea to run an action query in datasheet view first so you can see what records are affected?