Using results of a query to delete records

[cowboy3398]

I did not set up this database, I just have to work with it.

2 Tables, same strucutre (MTR_Course_Data, TMP_Course_Data1)
Lastname, Firstname, Birthdate, Mastered

MTR_Course_Data is the "master database"
TMP_Course_Data1 is an imported table which has additional records added to it.

The following query shows me the identical records in both tables:

SELECT MTR_Course_Data.*
FROM MTR_Course_Data INNER JOIN TMP_Course_Data1 ON (MTR_Course_Data.CourseDat_MasteryDate = TMP_Course_Data1.CourseDat_MasteryDate) AND (MTR_Course_Data.User_Birthdate = TMP_Course_Data1.User_Birthdate) AND (MTR_Course_Data.User_Firstname = TMP_Course_Data1.User_Firstname) AND (MTR_Course_Data.User_Lastname = TMP_Course_Data1.User_Lastname);

My question:
How do I delete the records returned by this query from TMP_Course_Data1 table, so I only have the new records left in TMP_Course_Data1?

 

[yetanotherjo]

A simple way to get around it is to update a field in the temp table duplicated record to something which flags it for deletion eg. "DeleteThis" then run a garden variety delete statement deleting records based on this field.

 

[PHV]

You may try something like this:
DELETE TMP_Course_Data1.*
FROM MTR_Course_Data INNER JOIN TMP_Course_Data1 ON (MTR_Course_Data.CourseDat_MasteryDate = TMP_Course_Data1.CourseDat_MasteryDate) AND (MTR_Course_Data.User_Birthdate = TMP_Course_Data1.User_Birthdate) AND (MTR_Course_Data.User_Firstname = TMP_Course_Data1.User_Firstname) AND (MTR_Course_Data.User_Lastname = TMP_Course_Data1.User_Lastname);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886