Last edit: 05-03-17 Graham Wideman

MS Access/Jet

Introduction

This is a summary of a very useful SQL pattern whose purpose is to "post" a table of updated or edited records into a cumulative master table having essentially the same structure. A single SQL UPDATE is all that's needed to handle both the update of existing records and the appending of new records because of the special behavior of the RIGHT JOIN in combination with UPDATE.

Details

Supposing we have a master table called Cumulative and an update table called Updates, and both have a structure as follows:

We want records in the Updates table to impact the Cumulative table as shown in the diagram. If an Update records doesn't match a record in Cumulative (based on Ix) then it should be appended. If it does match, then any differences in the Update record should update the Cumulative record.

The following query achieves this effect:

UPDATE Cumulative 
RIGHT JOIN Updates 
ON 
  Cumulative.Ix = Updates.Ix 
SET 
  Cumulative.Ix    = Updates.Ix, 
  Cumulative.Descr = Updates.Descr

It should be no surprise that this join query accomplishes the update of the matching records. The modestly surprising part is that this UPDATE query appends a record to the Cumulative table where no record matches the Updates row.

If however we had run this as an ordinary SELECT ... RIGHT JOIN query:

SELECT
  Cumulative.Ix, Cumulative.Descr, Updates.Ix, Updates.Descr
FROM 
  Cumulative RIGHT JOIN Updates 
ON Cumulative.Ix = Updates.Ix

...then the presence of rows in the result set contributed by Updates, with no matching values in the Cumulative columns  (ie: NULLS) would be unsurprising. So the only wrinkle in the UPDATE query is that these unmatched Updates rows cause rows to be appended to Cumulative.

Update Only Some Columns

In some scenarios, records in the Updates table may be only partial -- that is to say they may specify values for only some of the columns, with nulls for others. The intent might be to match an existing record in the Cumulative table and then alter only the columns with values in Updates, and leave the other columns alone. For this, the following variation on the query does the job. Note the IIf for the Descr column.

UPDATE Cumulative 
  RIGHT JOIN Updates 
ON Cumulative.Ix = Updates.Ix 
SET 
  Cumulative.Ix    = [Updates].[Ix], 
  Cumulative.Descr = IIf(IsNull(Updates.Descr), Cumulative.Descr, Updates.Descr)

Special Consideration for MS Access Autonumber Columns

When applying the above pattern in Access, if there are autonumber columns involved, these are columns which must be handled specially.

• If the source table (Updates) is used to autonumber (Ix in the example above), then the corresponding column in the target (Cumulative) table should be a simple long integer, so that it will freely accept the value from the source table.
   
• If the target table is used to autonumber the "incoming" records, them the UPDATE query should omit the autonumber column in the list of SETs, so that when creating new records Jet will autonumber that column instead of trying to SET it.

Note that autonumber situations have other considerations that need to be thought through. For example, if a table is empty and you compact the database, that resets autonumber columns to 0. This could wreak havoc if your application depends on having that table be the authority for some numbering sequence.

This could be the case in scenarios similar to the example above, where, after regularly posting Updates records to Cumulative, at some point you decide to clear all the records from the Updates table to keep it fast. To avoid this hazard you might preserve the highest-numbered record.

Go to: