gw_logo_08.gif (1982 bytes) 
Last edit: 05-03-17 Graham Wideman

MS Access/Jet

The "Posting" Pattern: SQL UPDATE with RIGHT JOIN
Article created: 2000-08-17


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.


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

Column Type
Ix Numeric, Primary Key
Descr String

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 
  Cumulative.Ix = Updates.Ix 
  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:

  Cumulative.Ix, Cumulative.Descr, Updates.Ix, Updates.Descr
  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 
  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.

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:  gw_logo_08.gif (1982 bytes)