if exists, update, else insert new record

Question

i am inserting values into a table

if the record exists already replace it, and if it does not exist then add a new one.

so far i have this code:

INSERT INTO table_name
VALUES (value1, value2, value3,...) where pk="some_id";

but i need something like this

if not pk="some_id" exists then  INSERT INTO table_name
    VALUES (value1, value2, value3,...) where pk="some_id"; else update table_name where pk="some_id"

what would be the correct SQL syntax for this?

please note that i am using sql access and that i guess it can be a combination of vba and sql

Answer 1

This could be done easily with recordsets. The code would then look like that (for an ADODB recordset):

myRecordset.find ....
if myRecordset.EOF then
    myRecordset.addNew
endif
....
myRecordset.fields(...) = ...
....
myRecordset.update

Answer 2

First update rows which match between your import table and master table.

UPDATE table_name AS m
    INNER JOIN tblImport AS i
    ON m.pk = i.pk
SET
    m.field2 = i.field2,
    m.field3 = i.field3,
    m.field4 = i.field4;

Then add any imported records which don't exist in the master table.

INSERT INTO table_name (
    pk,
    field2,
    field3,
    field4)
SELECT
    i.pk,
    i.field2,
    i.field3,
    i.field4
FROM
    tblImport AS i
    LEFT JOIN table_name AS m
    ON i.pk = m.pk
WHERE
    (((m.pk) Is Null));

Answer 3

I have posted about my approach to this problem many, many times in many different forums, but I'll just recapitulate the basic structure of the approach I use. There is no way to do it in one step, though.

1. update the existing records from the external data source.

2. insert records that don't already exist.

This assumes a common primary key that can be used to link the existing table with the external data source.

Task #2 is pretty trivial, just an outer join for the records that don't already exist.

One can use brute force for #1, writing an UPDATE statement with a SET for each field other than the primary key, but I consider that to be messy and unnecessary. Also, since I have a lot of replicated applications, I can't do that, as it would result in false conflicts (when a field is updated to the same value as it started with).

So, for that purpose, I use DAO and write an on-the-fly SQL statement to update COLUMN-BY-COLUMN. The basic structure is something like this:

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim strField As String
  Dim strSet As String
  Dim strWhere As String
  Dim strSQL As String

  Set db = CurrentDB
  Set rs = db.OpenRecordset("DestinationTable")
  For Each fld in rs.Fields
    strField = fld.Name
    If strField <> "PKField" Then
       strSet = "DestinationTable." & strField & " = ExternalTable." & strField
       strWhere = "Nz(DestinationTable." & strField & ",'') = Nz(ExternalTable." & strField & ", '')"
       strSQL = "UPDATE DestinationTable "
       strSQL = strSQL & " SET " & strSet
       strSQL = strSQL & " WHERE " & strWhere
       db.Execute strSQL, dbFailOnError
       Debug.Print strField & ": " & db.RecordsAffected
    End If
  Next fld

Now, the complicated part is handling numeric vs. date vs. string fields, so you have to have some logic to write the WHERE clauses to use proper quotes and other delimiters according to the field type. Rather than test the field type, I generally just use a CASE SELECT like this, making string fields the default:

  Dim strValueIfNull As String
  Select Case strField
    Case "DateField1", "DateField2", "NumericField2", "NumericField2", "NumericField3"
      strValueIfNull = "0"
    Case Else
      strValueIfNull = "''"
      strWhere = "Nz(DestinationTable." & strField & ", '') = Nz(ExternalTable." & strField & ", '')"
  End Select
  strWhere = "Nz(DestinationTable." & strField & ", " & strValueIfNull & ") = Nz(ExternalTable." & strField & ", " & strValueIfNull & ")"

I could have the details there wrong, but you get the idea, I think.

This means you'll run only as many SQL updates as there are updatable fields, and that you'll only update records that need updating. If you're also stamping your records with a "last updated" date, you'd do that in the UPDATE SQL and you'd only want to do that on the records that really had different values.