Coding Techniques for Upserts

David Clement

September 2004

Oracle versions 7-8

A long-standing limitation of SQL is that it does not handle the common situation where new data has to be put into a table either by inserting or by updating as appropriate. For example, if a salesperson is verifying corporate customers' orders by phone, it happens many times a day that the salesperson has to modify an existing order by adding 5 more cartons of medium widgets. If there is already an order line for medium widgets, it should be updated, but if not, a new order line should be inserted. This is called an "upsert" situation.

Because of the lack of any way to do this in standard SQL, many programmers have resorted to the following algorithm (expressed in C with embedded SQL).

          exec sql select count (*)
          into :hostvariable
          from t
          where a = :myRequirement;
          if (hostvariable == 0) {
              exec sql insert into t
              values (:myRequirement, :myNewValue);
          } else {
              exec sql update t
              set b = :myNewValue
              where a = :myRequirement;
          }

One problem with this code is that it performs a minimum of two SQL statements to do the work of one. Another problem is that it relies on a host-language variable, so it is not completely portable; that is, the code cannot be cut and pasted from one source-code file to another.

The work-around for these problems adopted by many other programmers has been to force an error, as follows (expressed in PL/SQL).

          update t 
          set b = :myNewValue
          where a = :myRequirement;
          if sql%notfound
          then
              insert into t
              values (:myRequirement, :myNewValue);
          end if;

This code is still not completely portable, in that it uses a host language feature, in the example, the PL/SQL cursor attribute (where Pro*C would use the SQLCA).

Since Oracle 8 introduced the returning clause, a portable solution is available. This is to use where not exists with returning as follows.

          insert into t
          (select :myRequirement, :myNewValue
          from dual
          where not exists 
              (select 1
              from t
              where a = :myRequirement))
          returning a into :retval;
          update t
          set b = :myNewvalue
          where a = :myRequirement
          and a <> :retval;

Since this code is pure SQL, it can be packaged and invoked in all Oracle environments. However, it provides little improvement in performance, since it still executes two SQL statements. Of course, if retval is an array, the code above can process many rows with those two statements.

Finally, Oracle 9 provided the first real solution to the "upsert" problem, by extending the SQL language with a new verb, merge. Here is an example of its use.

          merge into t
          using (a = :myRequirement)
          when not matched then
              insert
              values (:myRequirement, :myNewValue)
          when matched then 
          update 
          set b = :myNewvalue;