Compare and Upate Table

[praveen_khm]

Hey friends,

I am looking around for a way to compare two tables. Compare by what i mean is update the first table too. I have attached an example file. Here, There are two tables: "New Table" and "Original Table".

Now, I need to check if any new data exists in "New table". If yes, I need to append them to the Original Table. Everything is fine till now. However, if there are any entries in the "Original Table" which are not available in "New Table", then I need to remove the check in the "Original Table", meaning they are not existing or active. Can anybody find a way out for this please?

Thanks,
Praveen

 

[The_Doc_Man]

Praveen, I suggest Julius Caesar's approach - "divide and conquer"

Treat this as a two-fold (or more) problem and break up the pieces. Attack the pieces. At some point what you want will have occurred.

I cannot download databases to my primary work site so I have to speak in non-specific terms. Here is how I might approach the problem.

In each table, I would have a couple of yes-no fields for correlation purposes. One might say REC_CHG and the other might say REC_ABS. This won't work unless the tables share a usable prime key, though...

I would write some queries. One would set REC_ABS = TRUE for a record if the DCount is zero when searching for that record's prime key in the other table. Run two queries, one on each table comparing to the other. When you are done, REC_ABS will be true for each record in either table if there is no corresponding record in the other table. I.e. record is absent.

OK, the "DIFFERENT" is trickier but still possible. A query could contain a lot of fields and it would be hard for me to generalize it, but you can write an update query - or a SET of update queries - that sets REC_CHG to TRUE for any record for which corresponding fields differ. Do the previous query first so that you can exclude records where REC_ABS is TRUE - i.e. if the record is unmatched. For matching records, you can do the update of the yes/no fields of the JOINED records joined by their prime keys where ( table1.x <> table2.x ) or ( table1.y <> table2.y ) or ( table1.z <> table2.z ) or ....

Once you have identified the records requiring attention, you can write more action queries that select the marked records to add to the tables or update the tables or whatever else needs to be done.

Pleasant to do? Oh, no, it won't be. It will be tedious like crazy. But it will be something that can be done.

 

[praveen_khm]

Hey, thanks for the information.

Will give a try.