Delete Query

[Sean]

I have 2 tables each have the fields Material and Qty. Table 1 is the master
table and Table 2 is a table of Material and Qty I want to delete from Table
1. I cannot seem to get it to work, can someone help me?
Thanks

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.


This query will delete matching records in Table1.
DELETE
FROM Table1
WHERE EXISTS
(SELECT * FROM TABLE2
WHERE Table2.Material = Table1.Material
And Table1.Qty = Table2.Qty)


On the other hand, if you just want to change the QTY in Table1 based on the
Qty in Table2, you would need to use an update query.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County