Last edit: 05-03-17 Graham Wideman
|The "Posting" Pattern: SQL UPDATE with RIGHT
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:
|Ix||Numeric, Primary Key|
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.
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.