Update query/Delete Query

[Guest]

I pull data off "another" database & dump it into MY database. The main
table in MY db contains a list of courses, times, instructors, etc. When I
pull the data off the "other" db, it contains all current course information,
like times, rooms, instructors, etc. So I have created an update query that
updates all this information in MY db. So if an instructor is changed in the
"other" db, it is changed in MY db also. This all works fine. The problem
is that the "other" db is missing courses that have been cancelled. Those
courses are on MY database because I don't know they have been cancelled
until I do this dump. However, when I run the update query, the cancelled
courses don't get deleted from MY db. I don't want to clear the table first
because there are courses in there that are manually added & I don't want to
have to re-enter all that data. Is there a way to have the update query also
delete courses from MY table that are not on the "other" db table?

 

[John Spencer]

Well, you could delete all the courses in YOUR table that aren't in the
"other" table. The problem is that unless you have a way of identifying the
manually added courses, they will get deleted also. Do you have some field
(or fields) that identify the manually added records?

DELETE DistinctRow Y.*
FROM YourTable as Y LEFT JOIN OtherTable as O
ON Y.SomeField = O.SomeField
WHERE O.SomeField Is NULL AND Y.SomeFieldIdentifyingManualEntries is Not
Null

 

[Guest]

No, there is no way to identify them AND we can't add another column because
someone else created a whole lot of code that would really be screwed up if
we added another field.

 

[John Spencer]

Interesting. Adding a new field to a table normally would not screw up a
good design.

Well, I don't see any solution for you other than using an unmatched query
(use the query wizard) to identify records that are in YOUR table and not in
the other table. That will display the suspect records and then you can
choose which ones you want to delete.

A possibility is to add another table that identifies the manual records by
their primary key. The problem here is that from the sounds of it you would
have to manually enter the keys into this table and you would have to be
sure that all the manual entry records you wanted to keep were in the table.
With the three tables, you could probably construct a query to list the
records to be deleted and then examine that to make sure it contained only
the records you wanted to delete. If so, delete all; if not, hand delete or
edit your table of manually entered records and repeat the query.