Update if Record exist, otherwise Insert

[TheSmileyCoder]

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.

[yarbrough40]

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.

[ADezii]

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.

[NeoPa]

@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.

[TheSmileyCoder]

Then that answers my question just fine :)

As I wrote in the initial post, im perfectly capable of "working arond it", but every now and then when you do another work-around in another project, you stop and wonder if there is a smarter way of doing it.

[NeoPa]

It was a very sensible question, and well phrased (unambiguous). I have no doubt you're more than capable of handling the job without this feature, should that be required (which seems to be the case). No further explanation is necessary.

[ADezii]

It's nice knowing 'Who to Call', thanks.

[NeoPa]

Ghost Busters!

[yarbrough40]

ok thinking about this again the ADO approach could work. Since you are doing an action (Update, Delete) either way. Just build your ADODB recordset then update the record it returns using a dynamic cursor. If it errors that means that there is no record to update in which case just handle that error to run an AddNew.

[NeoPa]

I expect that would work, but as a non-SQL based approach I doubt it would fit the bill.

[yarbrough40]

but as a non-SQL based approach

I don't understand what you mean by "non-SQL"... oh- you mean you want to avoid any type of VBA manipulation?..... the recordset is really nothing more than a glorified query object as you know. Doubt if it would take any extra system resources to run it as opposed to a srtaight SQL.

[NeoPa]

Actually, I'm referring back to the OP's original request - for a SQL based solution.

In fact, using data manipulation within recordset code is generally orders of magnitude slower than straight SQL, but in this case that's beside the point really. It's only that the OP specifically requested a SQL based approach.

[yarbrough40]

Ahhh totally get it : ) you are correct. It is, though perhaps a good alternative to consider because it only makes a single call as opposed to a two step: 1) check for record 2) run action query.
the OP's original intent was to 'work smarter' afterall...

[TheSmileyCoder]

Well this is the code I went with in the end:

Public Sub setStatus(lngNode As Long, lngBidder As Long, lngObsType As Long, lngNewStatus As Long)
    Dim strSQL As String
    'Does record allready exist?
    If DCount("ID_Status", "tbl_EvalNodeStatus", "ID_EvalNode=" & lngNode & " AND ID_Bidder=" & lngBidder & " AND ID_ObsType=" & lngObsType) > 0 Then
        'Doupdate
            strSQL = "UPDATE tbl_EvalNodeStatus SET " & _
                    "ID_Status = " & lngNewStatus & "," & _
                    "ID_User = UserID()," & _
                    "dt_Changed = Now() " & _
                    "WHERE ((" & _
                    "(ID_Bidder)=" & lngBidder & ") AND (" & _
                    "(ID_EvalNode)=" & lngNode & ") AND (" & _
                    "(ID_ObsType)=" & lngObsType & "));"
    Else
            strSQL = "INSERT INTO tbl_EvalNodeStatus ( ID_Bidder, ID_EvalNode, ID_ObsType, ID_Status, ID_User, dt_Changed )" & _
                    " SELECT " & _
                    lngBidder & " AS IDBidder," & _
                    lngNode & " AS IDEvalNode," & _
                    lngObsType & " AS IDObsType," & _
                    lngNewStatus & " AS IDStatus, " & _
                    "Userid() AS idUser, " & _
                    "Now() AS dtWhen;"
 
    End If
 
    'Now perform update/insert
    DoCmd.SetWarnings (False)
        DoCmd.RunSQL strSQL
    DoCmd.SetWarnings (True)
 
End Sub

And for yarborough, I have on a few occasions found it an order of magnitude faster working with DAO recordsets over ADO. Whether this was specific to the tasks I was doing at the time or if its a general thing I don't know. Haven't bothered me enough to make me research it yet. :)

[yarbrough40]

yes - this seems to be the most intuitive way to approach it... a slightly different approach may be to skip the DLookUp and just run the queries. If the first one errors it will run the second Afterall you know you are going to fire an action query you just don't know which one. But doing this it doesn't matter. (it's basically a Try Catch)

On Error Goto AddNewRecord
 
'TRY THIS FIRST
strSQL = "UPDATE tbl_EvalNodeStatus SET " & _ 
                    "ID_Status = " & lngNewStatus & "," & _ 
                    "ID_User = UserID()," & _ 
                    "dt_Changed = Now() " & _ 
                    "WHERE ((" & _ 
                    "(ID_Bidder)=" & lngBidder & ") AND (" & _ 
                    "(ID_EvalNode)=" & lngNode & ") AND (" & _ 
                    "(ID_ObsType)=" & lngObsType & "));" 
DoCmd.SetWarnings (False) 
DoCmd.RunSQL strSQL 
DoCmd.SetWarnings (True) 
Exit Sub
 
'FIRE THIS ONE IF THE FIRST ONE ERRORS (NO RECORD)
AddNewRecord:
strSQL = "INSERT INTO tbl_EvalNodeStatus ( ID_Bidder, ID_EvalNode, ID_ObsType, ID_Status, ID_User, dt_Changed )" & _ 
                    " SELECT " & _ 
                    lngBidder & " AS IDBidder," & _ 
                    lngNode & " AS IDEvalNode," & _ 
                    lngObsType & " AS IDObsType," & _ 
                    lngNewStatus & " AS IDStatus, " & _ 
                    "Userid() AS idUser, " & _ 
                    "Now() AS dtWhen;" 
 
DoCmd.SetWarnings (False) 
 DoCmd.RunSQL strSQL 
DoCmd.SetWarnings (True) 
 
 

[ADezii]

I think that a Variation on yarbrough40's approach in Post #9 may be more effective.

1. Perform an outright Insert on the Table.
2. Trap the specific Error (Duplication on the Primary Key) that may occur should the Composite Primary Key already exist.
3. Update the existing Record, only if the Error occurs, otherwise the Insert will be successful.
4. This would have the advantage of occasionally being a 1-Step Process of Insertion when the Composite Key does not exist and avoids the use of DCount().

[yarbrough40]

YES ADezii !!! well said....

[ADezii]

Guess great minds just think alike! (LOL)

[TheSmileyCoder]

The update first, and Insert on error would not work. The update would run fine enough, just not change anything. (IE. no error).

ADezii makes a good point though.

[ADezii]

Since the Topic was brought up (ADO vs DAO), I'm going to go off on a slight Tangent here, mention a few simple points, and hope NeoPa doesn't holler at me! (LOL)

1. DAO provides greater functionality, generally better performance, is simpler to implement, and tends to be more efficient when working with Jet-based data.
2. ADO is a more 'modern' approach, and is more efficient than DAO when working with Server Data in code. Use ADO if you want to fill Recordsets with Server Data or to execute Server Commands.
3. Did HE see me?

[NeoPa]

@ADezii
I think what you were looking for is Fools seldom differ!

Just kidding of course, but how could you expect me to pass that one up. Just not reasonable :D

In this case, I suspect you are either overlooking, or are unaware of, the impact of interrupting a SQL process. Allowed to work at full speed, a SQL process can make use of many very powerful optimisations. When a SQL process that is dealing with numbers of records allows VBA processes to run within itself (generally true when processing through a recordset), these benefits are lost (or at least overshadowed by the delays introduced). VBA is a semi-compiled (partially interpreted) system that is frankly not the fastest system invented for code. It generally doesn't need to be. Forms and reports processing (EG handling events) clearly doesn't require super-fast execution. When you allow it to get in the way of a SQL process however (Forget smaller numbers. We're talking hundreds, thousands, millions of records here) the inefficiencies introduced on a per record basis are multiplied many-fold (x record numbers), hence it is always important to be careful of introducing recordset processing into large datasets.

All that said, it does appear that the actual process in this case (determined by Smiley's code example) is actually only a single record at a time anyway. Personally, I'd still go for the simple approach, but with a single record the processing difference would never be noticed, so a little extra complication can't hurt.
@ADezii
No. I was looking the other way at the time.

[ADezii]

I was looking at it from a perspective of an always 2-Step Process (DCount()/UPDATE or INSERT) vs an occasional 1-Step Process (INSERT ONLY), which would probably be more frequent due to the nature of the 3-Field Composite Key and the likelihood of duplication. Don't really think that you can argue with that logic.

[NeoPa]

@ADezii
Oh, that's easy! It's just more complicated than necessary.

I've already allowed that, in the circumstances, it hardly matters, but KISS is always an axiom worth following when there is no appreciable benefit from clever coding.

[ADezii]

Sorry, I'll still take efficiency over simplicity, any day.

[nico5038]

As stated, the one step approach won't be possible.

The most efficient way will be to remove all "update rows" on forehand using a DELETE query with a "WHERE EXISTS" and execute an INSERT to add all rows from the source.

This will save the effort of comparing/testing whether a row needs an update and by using two straight forwards queries the process will be a lot faster as the VBA appraoch as no code and no SQL-interpretation needs to be executed.

Nic;o)

[NeoPa]

@ADezii
I'm afraid I wasn't clear. The VBA approach is less efficient. It is just that it is by such a small margin when only one record is being processed (as appears to be the case in this instance) that it doesn't really matter.

If there's one benefit the VBA approach has over the straightforward SQL it is that it is very explicit as to what it's doing, for when someone comes to maintain it later.

[yarbrough40]

It's funny how I remembered this post as I sit here at work doing my daily grind. It never occurred to me before now but as it turns out I do have a solution for this: It's not a single query but it IS a single statement that fits the bill. I simply fire a delete statement followed by the insert every time (no need for an update since the delete/insert combo would wipe out any existing record if it exists.

The only way this may not be effective is if there is some unique data in the original record that needs to be maintained for some reason.

 DELETE FROM Table1 WHERE Col1 = 'A';INSERT INTO Table1(Col1,Col1)VALUES('B',1);

----as I just hit save on this, I read nico5038's post above who I believe is saying about the same thing.

[ADezii]

Sorry, but I still agree on the Attempt to ADD/Error Trap approach. It involves no Conditional Branching, no Deletions, no Lookups, and the Error Trap can be as sophisticated as you wish. I'm now putting on my Flak Jacket! (LOL).

Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Set db = CurrentDb
Set rst = db.OpenRecordset("tblTest", dbOpenDynaset, dbAppendOnly)
 
With rst
  .AddNew
    ![Field1] = 1
    ![Field2] = 2
    ![Field3] = 3
  .Update
End With
 
Exit_Add_Record:
  If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing
  End If
    Exit Sub
 
Err_Add_Record:
  'Check the State of the Error Number
  Select Case Err.Number
    Case 3022         'Duplicate PK
      MsgBox "Primary Key Violation"
    Case Else         'Some other Error
      MsgBox Err.Description
  End Select
    Resume Exit_Add_Record

[NeoPa]

I recently came across (triggered by a question here on Bytes strangely enough) the idea of an UPSERT (or similar) statement being introduced into SQL (Wikipedia - UPSERT).

Various good solutions have already been proposed within the thread, but I suspect, from reading the OP, that the simple matter of whether or not the facility exists was all Smiley was after. My earlier ramblings on performance and efficiency were related to a discussion on updates to a whole recordset (Therefore not directly related to this thread - though hopefully interesting to any that stumble across it).