How to delete non-matching records by comparing with a table

[Vinod]

Hi All,

I've two tables, tempMaster table contains 100 records (duplicate of master
table) and child table contains part of hundred records. I would like to
delete all reccords in tempMaster table which are not matching with child
table .

E.g:
tempMaster table
Theater Country Product_ID column1 Column2 Column3
APAC INDIA xyz test1 test2 test3
APAC INDIA xyz1 test1 test2 test3
APAC China xyz2 test1 test2 test3
JAPAN Japan xyz3 test1 test2 test3
JAPAN Japan xyz4 test1 test2 test3
JAPAN Japan xyz5 test1 test2 test3

child table
Theater Country Product_ID
APAC INDIA xyz
APAC China xyz2
JAPAN Japan xyz5

Note: Product_ID is unique.
tempMaster table (Theater, Country and Product_ID) is copy of master table
with same no.of records.

My requirement is I need to delete the records in tempMaster table which are
not part of child table.
i.e., we can delete non-matching Product_IDs in tempMaster table by
comparing with child table Product_IDs.

After deleting records I should see Product_IDs in tempMaster table are
xyz,xyz2 and xyz5 only.

Please share your thoughts and ideas in deleting non-matching records as
mentioned above which will be appreciated.

Advanced Thanks
~Vins

 

[AL-MURISI]

Hi All,

I've two tables, tempMaster table contains 100 records (duplicate of
master
table) and child table contains part of hundred records. I would like to
delete all reccords in tempMaster table which are not matching with child
table .

E.g:
tempMaster table
Theater Country Product_ID column1 Column2 Column3
APAC INDIA xyz test1 test2 test3
APAC INDIA xyz1 test1 test2 test3
APAC China xyz2 test1 test2 test3
JAPAN Japan xyz3 test1 test2 test3
JAPAN Japan xyz4 test1 test2 test3
JAPAN Japan xyz5 test1 test2 test3

child table
Theater Country Product_ID
APAC INDIA xyz
APAC China xyz2
JAPAN Japan xyz5

Note: Product_ID is unique.
tempMaster table (Theater, Country and Product_ID) is copy of master table
with same no.of records.

My requirement is I need to delete the records in tempMaster table which
are
not part of child table.
i.e., we can delete non-matching Product_IDs in tempMaster table by
comparing with child table Product_IDs.

After deleting records I should see Product_IDs in tempMaster table are
xyz,xyz2 and xyz5 only.

Please share your thoughts and ideas in deleting non-matching records as
mentioned above which will be appreciated.

Advanced Thanks
~Vins

 

[Ken Sheridan]

This should do it:

DELETE *
FROM tempMaster
WHERE NOT EXISTS
(SELECT *
FROM [child table]
WHERE [child table].Product_ID = tempMaster.Product_ID);

Ken Sheridan
Stafford, England

 

[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.

Delete all records in TEMPMaster that don't have a matching product id
in the child table. Theater and country do not have an effect on the
records that are deleted

DELETE
FROM tempMaster
WHERE Product_ID NOT IN
(SELECT Product_ID FROM [ChildTable] WHERE Product_ID is not Null)

If this is too slow you can build a faster query, but the above is the
simplest if you have a small list of records.

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