Update Query (replacing entrie records)

[kshope25]

I have two tables (Base Table and Changes Table) with identical fields and field names. All records in the "changes table" need to be moved over to the base table, and the same records within that base table that already exist need to be removed/replaced. I have a field called "supplier code", and that's how i want the query to know when to remove/replace the data.

Basically, the query needs to look at the change table and see what all supplier codes exist, go remove those in the "base table, and replace with the ones in the "change table".

Any help would be appreciated.

 

[jdraw]

Having 2 tables with exactly the same structure and names is not necessarily a bad thing, but it is rather rare. Perhaps you should tell us more of your application and intent and see if there are other options.

 

[kshope25]

The changes table is basically a testing table, where we can test different options that we want to test. They are transportation tables, so if we want to change a pick up time, or a pick up day for a certain supplier, we do it in the "changes table" or testing table. Once we get all of the changes finalized and looking how we want, we need to move these changes to our live table (or base table in this case)

 

[Pat Hartman]

Create a query that joins the two tables. Select all the columns except the PK from the table you want to update. Change the query type to UPDATE. Then choose the correct field from the test table for each column in the main table.