MS Access:如何实现ON DUPLICATE KEY UPDATE? [英] MS Access: how to achieve ON DUPLICATE KEY UPDATE?

问题描述

尝试避免使用单独的查询来检查键是否存在，然后相应地进行插入或更新.从谷歌搜索的角度来看，Access似乎也不支持SQL Server的MERGE.

Trying to avoid separate queries to check for key existance and then inserting or updating accordingly. From Googling around it seems that Access doesn't support SQL server's MERGE either.

谢谢

推荐答案

实际上，Access确实支持"Upserting"(有时被称为"Upserting").我在由Arvin Meyer和Douglas J. Steele撰写的名为 Microsoft Access解决方案-Microsoft Access MVP的提示，技巧和秘密的书中找到了该解决方案.

Actually, Access does support "Upserting" as it is sometimes called. I found this solution in a book called Microsoft Access Solutions - Tips, Tricks and Secrets from Microsoft Access MVPs written by Arvin Meyer and Douglas J. Steele.

这是一个查询，该查询从Contacts1表中获取新数据并将其应用于Contacts2表:

Here's a query that takes the new data from the Contacts1 table and applies it to the Contacts2 table:

UPDATE Contacts2 LEFT JOIN Contacts1
ON Contacts2.ContactID = Contacts1.ContactID
SET Contacts2.ContactID = [Contacts1].[ContactID],
Contacts2.FirstName = [Contacts1].[FirstName],
Contacts2.LastName = [Contacts1].[LastName],
Contacts2.MobilePhone = [Contacts1].[MobilePhone]

自然，我认为您可以使用表别名和where条件.而且我不确定是否需要如图所示的括号，但我将其放在其中，因为书中的示例显示了它们(即使表名和字段名不包含空格或符号).

Naturally, I think you could use table aliases and where conditions. And I'm not sure the brackets are needed as shown but I put them in there since the example in the book shows them (even though the table names and field names contain no spaces or symbols).

这是从书中直接摘录的另一条注释:

And here's another note taken directly from the book:

很明显，只有在为每个表定义了唯一索引的情况下，此方法才有效.通常，这将是表的主键，但是实际上只要两个表中都存在候选键，就没有必要. (这使您可以将AutoNumber用作主键，而不必担心数字不同.)只要索引中包含单个字段还是复合键索引都没有关系，只要它与这两个表.

这篇关于MS Access:如何实现ON DUPLICATE KEY UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！