如果存在记录则更新，否则插入 [英] Update if Record exist, otherwise Insert

问题描述

如果记录已经存在，我需要更新表中的记录，但是如果它不存在，我需要插入记录。

是否有一步SQL这样做的方式？


我可以轻松地执行两步方法，使用dLookup来确定它是否存在，并根据它执行插入或更新，我是只是想更聪明地工作，而不是更努力：P


其他信息：Access 2003，拆分前端/后端，两个访问。

表由一个主键组成由3列组成， ID_EvalNode ， ID_ObsType ， ID_Bidder 和3个额外的信息字段， ID_User，dt_DateChanged，ID_Status。

I need to update a record in a table if the record allready exists, but if it does not exist, I need to insert the record.

Is there a 1-step SQL way of doing this?

I can easily perform the 2 step approach, of using a dLookup to determine if it exists, and based on that do either the Insert or Update, I''m just trying to work smarter, not harder :P

Other Info: Access 2003, Split frontend/Backend, both Access.
Table consists of a Primary Key made up of 3 columns, ID_EvalNode, ID_ObsType, ID_Bidder and 3 extra info fields, ID_User, dt_DateChanged, ID_Status.

推荐答案

我唯一能想到的是使用首先查找现有记录的单个ADO记录集。然后使用相同的记录集，执行Update或AddNew / Update取决于。

The only thing I can think of is to use a single ADO recordset that first looks for your existing record. Then using that same recordset, perform an Update or AddNew / Update depending.

我不认为这是一步一步的基于SQL的过程，因为它确实涉及条件分支。唯一能让我看到这种情况的方法是通过子查询中的EXISTS SQL谓词和IIf（）。我会在这个问题上打电话给''SQL Gang'。

I do not see this as a 1-Step SQL Based Process since it does involve Conditional Branching. The only was that I can possibly see this happening is via the EXISTS SQL Predicate in a Sub-Query in conjunction with IIf(). I''ll call in the ''SQL Gang'' on this one.

@TheSmileyOne


自从你问 - 不，没有。


不幸的是我知道，但我从来没有遇到任何支持这个概念的SQL，尽管显然它的要求是非常真实。

@TheSmileyOne
Since you ask - No. There''s not.

Unfortunate I know, but I''ve never come across anything in SQL that supports this concept, though clearly the requirement for it is very real.

这篇关于如果存在记录则更新，否则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！