INSERT OR UPDATE

Adds a new row or updates an existing row in a table.

Synopsis

INSERT OR UPDATE [%NOFPLAN] [restriction] [INTO] table
          SET column = scalar-expression {,column2 = scalar-expression2} ...  |
          [ (column{,column2} ...) ] VALUES (scalar-expression {,scalar-expression2} ...)  |
          VALUES :array()  |
          [  (column{,column2} ...) ] query  |
          DEFAULT VALUES

Arguments

Argument	Description
%NOFPLAN	Optional — The %NOFPLAN keyword specifies that Caché will ignore the frozen plan (if any) for this operation and generate a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans in Caché SQL Optimization Guide.
restriction	Optional — One or more of the following keywords, separated by spaces: %NOLOCK, %NOCHECK, %NOINDEX, %NOTRIGGER.
table	The name of the table or view on which to perform the insert operation. This argument may be a subquery. The INTO keyword is optional.
column	Optional — A column name or comma-separated list of column names that correspond in sequence to the supplied list of values. If omitted, the list of values is applied to all columns in column-number order.
scalar-expression	A scalar expression or comma-separated list of scalar expressions that supplies the data values for the corresponding column fields.
:array()	Embedded SQL only — A dynamic local array of values specified as a host variable. The lowest subscript level of the array must be unspecified. Thus :myupdates(), :myupdates(5,), and :myupdates(1,1,) are all valid specifications.
query	A SELECT query whose result set supplies the data values for the corresponding column fields for one or more rows.

Description

The INSERT OR UPDATE statement is an extension of the INSERT statement (which it closely resembles):

If the specified record does not exist, INSERT OR UPDATE performs an INSERT.
If the specified record already exists, INSERT OR UPDATE performs an UPDATE. It updates the record with the specified field values. An update occurs even when the specified data is identical to the existing data.

INSERT OR UPDATE determines of a record exists by matching UNIQUE KEY field values to the existing data values. If a UNIQUE KEY violation occurs, INSERT OR UPDATE performs an update operation. Note that a UNIQUE KEY field value may not be a value explicitly specified in INSERT OR UPDATE; it may be the result of a column default value or a computed value.

Note:

Because the %NOCHECK keyword disables unique value checking, INSERT OR UPDATE %NOCHECK always results in an insert operation. For this reason, do not specify %NOCHECK.

INSERT OR UPDATE of a single record always sets the %ROWCOUNT variable to 1, and the %ROWID variable for the row that has been either inserted or updated.

An INSERT OR UPDATE statement combined with a SELECT statement can insert and/or update multiple table rows. For further details, refer to “INSERT Query Results” in the INSERT reference page.

INSERT OR UPDATE uses the same syntax, and generally has the same features and restrictions as the INSERT statement. Special considerations for INSERT OR UPDATE are described here. Unless otherwise stated here, refer to INSERT for details.

Privileges

INSERT OR UPDATE requires both INSERT and UPDATE privileges. You must have these privileges either as table-level privileges or as column-level privileges. For table-level privileges:

The user must have both INSERT and UPDATE privileges on the specified table, regardless of the operation actually performed.
If inserting or updating data from another table using a SELECT query, the user must have SELECT privilege on that table.

If the user is the Owner (creator) of the table, the user is automatically granted all privileges for that table. Otherwise, the user must be granted privileges for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' is not privileged for the operation. You can determine if the current user has the appropriate privileges by invoking the %CHECKPRIV command. You can use the GRANT command to assign the user table privileges. For further details, refer to Privileges in Using Caché SQL.

IDKEY Fields

You can insert an IDKEY field value, but you cannot update an IDKEY field value. If the table has an IDKEY index and another unique key constraint, INSERT OR UPDATE matches these fields to determine whether to perform an insert or an update. If the other key constraint fails, this forces INSERT OR UPDATE to perform an update rather than an insert. However, if the specified IDKEY field values do not match the existing IDKEY field values, this update fails and generates an SQLCODE -107 error, because the update is attempting to modify the IDKEY fields.

For example, the table MyTest is defined with four fields: A, B, C, D, with IDKEY (A,B) and UNIQUE (C,D) constraints. The table contains the following records:

Row 1: A=1, B=1, C=2, D=2
Row 2: A=1, B=2, C=3, D=4

You invoke INSERT OR UPDATE ABC (A,B,C,D) VALUES (2,2,3,4) Because the UNIQUE (C,D) constraint failed, this statement cannot perform an insert. Instead, it attempts to update Row 2. The IDKEY for Row 2 is (1,2), so the INSERT OR UPDATE statement would attempt to change the field A value from 1 to 2. But you cannot change an IDKEY value, so the update fails with an SQLCODE -107 error.

Counter Fields

When an INSERT OR UPDATE is executed, Caché initially assumes the operation will be an insert. Therefore, it increments by 1 the internal counters used to supply integers to SERIAL (%Library.CounterOpens in a new tab) fields. An insert uses these incremented counter values to assign integer values to these fields. If, however, Caché determines that the operation needs to be an update, INSERT OR UPDATE has already incremented the internal counters, but it does not assign these incremented integer values to counter fields. If the next operation is an insert, this results in a gap in the integer sequence for these fields. This is shown in the following example:

The internal counter value is 4. INSERT OR UPDATE increments the internal counter then inserts Row 5: internal counter=5, SERIAL field value=5.
INSERT OR UPDATE increments the internal counter then determines that it must performs an update on an existing row: internal counter=6, no change to field counters.
INSERT OR UPDATE increments internal counter then inserts a row: internal counter=7, SERIAL field value=7.

IDENTITY and RowID Fields

The effect of INSERT OR UPDATE on the assignment of RowId values depends on whether an IDENTITY field is present:

If no IDENTITY field is defined for the table, an insert operation causes Caché to automatically assigns the next sequential integer value to the ID (RowID) field. Update operations have no effect on subsequent inserts. Thus, INSERT OR UPDATE performs the same insert operation as INSERT.
If an IDENTITY field is defined for the table, an INSERT OR UPDATE causes Caché to increment by 1 the internal counter used to supply integers to the IDENTITY field before determining if the operation will be an insert or an update. An insert operation assigns this incremented counter value to the IDENTITY field. If, however, Caché determines that the INSERT OR UPDATE operation needs to be an update, it has already incremented the internal counter, but it does not assign these incremented integer value. If the next INSERT OR UPDATE operation is an insert, this results in a gap in the integer sequence for the IDENTITY field. The RowID field value is taken from the IDENTITY field value, resulting in a gap in the assignment of ID (RowID) integer values.

Examples

The following five examples: create a new table (SQLUser.CaveDwellers); use INSERT OR UPDATE to populate this table with data, use INSERT OR UPDATE to add new rows and update existing rows; use a SELECT * to display the data; and delete the table.

The following example uses CREATE TABLE to create a table with a unique field (Num):

CreateTable
   ZNSPACE "Samples"
   &sql(CREATE TABLE SQLUser.CaveDwellers (
  Num          INT UNIQUE,
  CaveCluster  CHAR(80) NOT NULL,
  Troglodyte   CHAR(50) NOT NULL,
  CONSTRAINT CaveDwellerPK PRIMARY KEY (Num))
  )
  IF SQLCODE=0 {WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 {WRITE !,"Table already exists"}
  ELSE {WRITE !,"CREATE TABLE failed. SQLCODE=",SQLCODE }

The following example uses a class definition to define the same table, defining a unique key for Num:

   Class SQLUser.CaveDwellers Extends %Persistent [ 
      DdlAllowed,Owner={UnknownUser},SqlRowIdPrivate,
      SqlTableName=CaveDwellers ]
  { 
  Property Num As %Integer;
  Property CaveCluster As %String(MAXLEN=80);
  Property Troglodyte As %String(MAXLEN=50);
  Index UniqueNumIdx On Num [ Type=index,Unique ];
  }

SELECT * FROM SQLUser.CaveDwellers ORDER BY Num

Run the following two examples one or more times in any order. They will insert records 1 thorough 5. If record 4 already exists, INSERT OR UPDATE will update it. Use the SELECT * example to display the table data:

InsertOrUpdateIndividualRecords
  ZNSPACE "Samples"
  &sql(INSERT OR UPDATE INTO SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES 
    (1,'Bedrock','Flintstone,Fred'))
  IF SQLCODE = 0 { SET rcount=%ROWCOUNT }
  &sql(INSERT OR UPDATE INTO SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES 
    (4,'Bedrock','Flintstone,Wilma'))
  IF SQLCODE = 0 { SET rcount=rcount+%ROWCOUNT 
                   WRITE !,rcount," records inserted/updated" }
  ELSE { WRITE !,"Insert/Update failed, SQLCODE=",SQLCODE }

InsertOrUpdateWithQueryResults
   NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT OR UPDATE SQLUser.CaveDwellers
      (Num,CaveCluster,Troglodyte)
       SELECT %ID,Home_City,Name
       FROM Sample.Person
       WHERE %ID BETWEEN 2 AND 5)
    IF SQLCODE=0 {
    WRITE !,"Insert/Update succeeded"
    WRITE !,%ROWCOUNT," records inserted/updated"
    WRITE !,"Row ID=",%ROWID }
    ELSE {
    WRITE !,"Insert/Update failed, SQLCODE=",SQLCODE }

The following example deletes the table:

DeleteTable
  ZNSPACE "Samples"
  &sql(DROP TABLE SQLUser.CaveDwellers)
  IF SQLCODE=0 {WRITE !,"Table deleted" }
  ELSEIF SQLCODE=-30 {WRITE !,"Table does not exist"}
  ELSE {WRITE !,"DROP TABLE failed. SQLCODE=",SQLCODE }