By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Update if Record exist, otherwise Insert

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 13 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
28 Replies


yarbrough40
100+
P: 320
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.
Mar 15 '10 #2

ADezii
Expert 5K+
P: 8,601
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.
Mar 15 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Mar 15 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 15 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 15 '10 #6

ADezii
Expert 5K+
P: 8,601
It's nice knowing 'Who to Call', thanks.
Mar 15 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
Ghost Busters!
Mar 15 '10 #8

yarbrough40
100+
P: 320
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.
Mar 15 '10 #9

NeoPa
Expert Mod 15k+
P: 31,186
I expect that would work, but as a non-SQL based approach I doubt it would fit the bill.
Mar 15 '10 #10

yarbrough40
100+
P: 320
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.
Mar 15 '10 #11

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 15 '10 #12

yarbrough40
100+
P: 320
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...
Mar 15 '10 #13

TheSmileyCoder
Expert Mod 100+
P: 2,321
Well this is the code I went with in the end:
Expand|Select|Wrap|Line Numbers
  1. Public Sub setStatus(lngNode As Long, lngBidder As Long, lngObsType As Long, lngNewStatus As Long)
  2.     Dim strSQL As String
  3.     'Does record allready exist?
  4.     If DCount("ID_Status", "tbl_EvalNodeStatus", "ID_EvalNode=" & lngNode & " AND ID_Bidder=" & lngBidder & " AND ID_ObsType=" & lngObsType) > 0 Then
  5.         'Doupdate
  6.             strSQL = "UPDATE tbl_EvalNodeStatus SET " & _
  7.                     "ID_Status = " & lngNewStatus & "," & _
  8.                     "ID_User = UserID()," & _
  9.                     "dt_Changed = Now() " & _
  10.                     "WHERE ((" & _
  11.                     "(ID_Bidder)=" & lngBidder & ") AND (" & _
  12.                     "(ID_EvalNode)=" & lngNode & ") AND (" & _
  13.                     "(ID_ObsType)=" & lngObsType & "));"
  14.     Else
  15.             strSQL = "INSERT INTO tbl_EvalNodeStatus ( ID_Bidder, ID_EvalNode, ID_ObsType, ID_Status, ID_User, dt_Changed )" & _
  16.                     " SELECT " & _
  17.                     lngBidder & " AS IDBidder," & _
  18.                     lngNode & " AS IDEvalNode," & _
  19.                     lngObsType & " AS IDObsType," & _
  20.                     lngNewStatus & " AS IDStatus, " & _
  21.                     "Userid() AS idUser, " & _
  22.                     "Now() AS dtWhen;"
  23.  
  24.     End If
  25.  
  26.     'Now perform update/insert
  27.     DoCmd.SetWarnings (False)
  28.         DoCmd.RunSQL strSQL
  29.     DoCmd.SetWarnings (True)
  30.  
  31. 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. :)
Mar 15 '10 #14

yarbrough40
100+
P: 320
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)
Expand|Select|Wrap|Line Numbers
  1. On Error Goto AddNewRecord
  2.  
  3. 'TRY THIS FIRST
  4. strSQL = "UPDATE tbl_EvalNodeStatus SET " & _ 
  5.                     "ID_Status = " & lngNewStatus & "," & _ 
  6.                     "ID_User = UserID()," & _ 
  7.                     "dt_Changed = Now() " & _ 
  8.                     "WHERE ((" & _ 
  9.                     "(ID_Bidder)=" & lngBidder & ") AND (" & _ 
  10.                     "(ID_EvalNode)=" & lngNode & ") AND (" & _ 
  11.                     "(ID_ObsType)=" & lngObsType & "));" 
  12. DoCmd.SetWarnings (False) 
  13. DoCmd.RunSQL strSQL 
  14. DoCmd.SetWarnings (True) 
  15. Exit Sub
  16.  
  17. 'FIRE THIS ONE IF THE FIRST ONE ERRORS (NO RECORD)
  18. AddNewRecord:
  19. strSQL = "INSERT INTO tbl_EvalNodeStatus ( ID_Bidder, ID_EvalNode, ID_ObsType, ID_Status, ID_User, dt_Changed )" & _ 
  20.                     " SELECT " & _ 
  21.                     lngBidder & " AS IDBidder," & _ 
  22.                     lngNode & " AS IDEvalNode," & _ 
  23.                     lngObsType & " AS IDObsType," & _ 
  24.                     lngNewStatus & " AS IDStatus, " & _ 
  25.                     "Userid() AS idUser, " & _ 
  26.                     "Now() AS dtWhen;" 
  27.  
  28. DoCmd.SetWarnings (False) 
  29.  DoCmd.RunSQL strSQL 
  30. DoCmd.SetWarnings (True) 
  31.  
  32.  
Mar 15 '10 #15

ADezii
Expert 5K+
P: 8,601
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().
Mar 15 '10 #16

yarbrough40
100+
P: 320
YES ADezii !!! well said....
Mar 15 '10 #17

ADezii
Expert 5K+
P: 8,601
Guess great minds just think alike! (LOL)
Mar 15 '10 #18

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 15 '10 #19

ADezii
Expert 5K+
P: 8,601
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?
Mar 15 '10 #20

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Mar 15 '10 #21

ADezii
Expert 5K+
P: 8,601
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.
Mar 15 '10 #22

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Mar 15 '10 #23

ADezii
Expert 5K+
P: 8,601
Sorry, I'll still take efficiency over simplicity, any day.
Mar 16 '10 #24

nico5038
Expert 2.5K+
P: 3,072
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)
Mar 16 '10 #25

NeoPa
Expert Mod 15k+
P: 31,186
@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.
Mar 16 '10 #26

yarbrough40
100+
P: 320
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.

Expand|Select|Wrap|Line Numbers
  1.  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.
Aug 2 '11 #27

ADezii
Expert 5K+
P: 8,601
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).
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set db = CurrentDb
  5. Set rst = db.OpenRecordset("tblTest", dbOpenDynaset, dbAppendOnly)
  6.  
  7. With rst
  8.   .AddNew
  9.     ![Field1] = 1
  10.     ![Field2] = 2
  11.     ![Field3] = 3
  12.   .Update
  13. End With
  14.  
  15. Exit_Add_Record:
  16.   If Not rst Is Nothing Then
  17.     rst.Close
  18.     Set rst = Nothing
  19.   End If
  20.     Exit Sub
  21.  
  22. Err_Add_Record:
  23.   'Check the State of the Error Number
  24.   Select Case Err.Number
  25.     Case 3022         'Duplicate PK
  26.       MsgBox "Primary Key Violation"
  27.     Case Else         'Some other Error
  28.       MsgBox Err.Description
  29.   End Select
  30.     Resume Exit_Add_Record
Aug 2 '11 #28

NeoPa
Expert Mod 15k+
P: 31,186
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).
Aug 3 '11 #29

Post your reply

Sign in to post your reply or Sign up for a free account.