append vs update query

  • Thread starter Thread starter Dick Minter
  • Start date Start date
D

Dick Minter

When using an append query where the source table and
target table are identical, including key fields, how does
the query handle duplicate keys? Can I use a query to
append records with no duplicate key in the target, and to
update records in the target where the keys are the same?
 
Hi,


A key cannot have twice the same value, in the same table.

In Jet, with DAO, the record creating the duplication would not be appended, but other records
would be appended. With MS SQL Server, by default, the record creating the duplication would not be
appended and neither the other records will be appended, unless you use the option to ignore
duplicated values, IGNORE_DUP_KEY, in which cases, MS SQL Server will behave like Jet with DAO.



Hoping it may help,
Vanderghast, Access MVP
 
Dear Dick:

You can filter the query for inserting so it would not attempt to make a
duplicate. In the general case, a WHERE NOT EXISTS() clause would be useful
for this, but when the key you want to ensure against duplication is on a
single column, you can also use WHERE NOT IN().

Reversing the above to WHERE EXISTS() or WHERE IN() you can filter the
UPDATE query to perform its work only on those rows that have already been
added.

This leaves a third case, possibly: the case in which there is a duplicate
in the source table. This should be tested and handled first. Just how to
handle it depends on the requirements within your data.

Also, there is the question of what happens to the old information in the
destination table when you UPDATE. As discussed so far, this information
will be lost. You may want to give some thought whether this is exactly
what is desired.

I like to get answers to all such questions before even beginning to tackle
the actual coding of something like this. But, it certainly comes up
several times a year!
 
Hi,


With Jet, yes (but not for MS SQL Server).


UPDATE oldTable RIGHT JOIN updatingData
ON oldTable.pk=updatingData.pk

SET oldTable.pk=updatingData.pk,
oldTable.f1=updatingData.f1,
oldTable.f2=updatingData.f2,
oldTable.f3=updatingData.f3,
...


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top