MS Access UPSERT(更新/插入)SQL [英] MS Access UPSERT (Update/Insert) SQL

问题描述

我知道这个问题一定已经问了一百万遍了，但是我还没有在MS Access 2007 SQL中找到解决此问题的"NON-VBA"解决方案.

I know this question must have been asked a million times but I have yet to find a "NON-VBA" solution to this in MS Access 2007 SQL.

我正在使用2个表 TBLDestination 和 TBLSource .找到匹配的ID后，我必须从源表记录中更新目标表记录.对于不匹配的ID(即新ID)，我想插入新记录 (请参阅下表).

I am using 2 Tables TBLDestination and TBLSource. I have to update the destination table records from source table records when a matching ID is found. For a non-matching ID (i.e. new ID) I want to insert the new record (Please refer the tables below).

-----TBLSource-------
ID (match if ID exists in Destination table)
EmpName
EmpAdd

---TBLDestination-----
ID
EmpName (to be updated/inserted)
EmpAdd (to be updated/inserted)
Salary

推荐答案

MS Access没有与UPSERT等效的功能，您可以在单个查询中完成这两个功能.

MS Access doesn't have an equivalent to UPSERT where you can do both in a single query.

但是，通过执行UPDATE查询，您可以在连接到要更新的表的地方获得相同的效果.然后，您执行INSERT查询，在其中将表OUTER JOIN插入，并仅返回不存在的表.

However, you can get the same effect by doing an UPDATE query where you join to the table you want to update from. Then you do an INSERT query where you OUTER JOIN to the table and return only those where it doesn't exist.

这篇关于MS Access UPSERT(更新/插入)SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！