INSERT OR UPDATE (SQL)

Single Row Inserts or Updates

• INSERT OR UPDATE table (column, column2, ...) VALUES (value, value2, ...) inserts or updates a row of values for the specified columns of a table. The values in the VALUES clause must correspond positionally with the column names in the column list. The insert or update of a single row sets the %ROWCOUNT variable to 1 and the %ROWID variable to the inserted or updated row.

  This statement first tries inserting a new row of data into the Sample.Records table. If the RecordID column enforces a UNIQUE constraint and the RecordID being inserted already exists, then INSERT OR UPDATE updates the existing row instead.

  INSERT OR UPDATE Sample.Records (RecordID,StatusDate,Status) VALUES (105,'05/12/22','Purged')

  

  Example: Insert or Update Rows in a Table

• INSERT OR UPDATE table VALUES (value, value2, ...) inserts or updates the table row of values in column number order. The data values must correspond positionally to the defined column list. You must specify a value for every specifiable table column. You cannot use defined default values, but you can specify an empty string as a value. Because the RowID column is not specifiable, do not include a RowID value in the VALUES list.

  This statement first tries inserting a row of four values into the Sample.Address table in order. If this combination of columns has a unique constraint, and a value for this key is already defined in the table, then INSERT OR UPDATE updates the existing row instead.

  INSERT OR UPDATE Sample.Address VALUES ('22 Main St.','Anytown','PA','65342')

  

• INSERT OR UPDATE table SET column = value, column2 = value2, ... inserts or updates a row of values by explicitly setting the values of specific columns.

  This statement performs the same operation as in the INSERT OR UPDATE table (column, column2, ...) VALUES (value, value2, ...) syntax.

  INSERT OR UPDATE Sample.Records SET RecordID=105, StatusDate='05/12/22',Status='Purged'

  

• INSERT OR UPDATE table DEFAULT VALUES inserts or updates a row that contains only default column values.

  • Columns with a defined default value are set to that value.

  • Columns without a defined default value are set to NULL.

  This statement inserts a row of default column values into the Sample.Person table.

  INSERT OR UPDATE Sample.Person DEFAULT VALUES

  

• INSERT OR UPDATE table VALUES :array() inserts or updates values from an array, specified as a host variable, into the columns of a table. You can use this syntax with Embedded SQL only. The values in this array must implicitly correspond to the columns of the row in column number order. You must specify a value for each specifiable column. An INSERT OR UPDATE using column order cannot take defined column default values.

  This class method uses embedded SQL to insert into or update an array for the Sample.FullName table. myarray(1) is reserved for the RowID column and is therefore not specified.

  ClassMethod EmbeddedSQLInsertOrUpdateHostVarArray()
  {
    set myarray(2)="Juanita"
    set myarray(3)="Pybus"
    &sql(INSERT OR UPDATE Sample.FullName VALUES :myarray())
    if SQLCODE '= 0 {
      write !, "Insert or update failed, SQLCODE= ", SQLCODE, ! ,%msg
      quit
    }
    write !,"Insert or update succeeded" quit
  }

  

  For more details on host variables and arrays, see Host Variable as a Subscripted Array.

Multi-Row Inserts or Updates

• INSERT OR UPDATE table query inserts or updates rows of data the come from the result set of a SELECT query. The columns in the result set must match the columns in the table. You can use INSERT OR UPDATE with a SELECT to populate a table with existing data extracted from other tables.

  This statement inserts the Name row from the Sample.Customer table into the Sample.Person table, or updates existing rows of Sample.Person with the corresponding Sample.Customer values.

  INSERT OR UPDATE Sample.Person SELECT Name FROM Sample.Customer

  

• INSERT OR UPDATE table (column, column2, ...) query inserts or updates rows of data from the query result set into the specified columns.

  This statement inserts or updates the query result set data from the Name and DOB columns of Sample.Person into the matching columns of the Sample.Kids table.

  INSERT OR UPDATE Sample.Kids (Name,DOB) SELECT Name,DOB FROM Sample.Person WHERE Age <= 18

  

Insert or Update Options

• INSERT OR UPDATE INTO table ... specifies the optional INTO keyword.

• INSERT OR UPDATE %keyword [INTO] table ... sets one or more %keyword options, separated by spaces. Valid options are %NOCHECK, %NOFPLAN, %NOINDEX, %NOJOURN, %NOLOCK, %NOTRIGGER, %PROFILE, and %PROFILE_ALL.

  Note:

  Because the %NOCHECK keyword disables unique value checking, INSERT OR UPDATE %NOCHECK always results in an insert operation and is therefore equivalent to INSERT.

table

The name of the table or view on which to perform the insert operation. This argument can also be a subquery.

column

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.

IDKEY column values can be inserted but not updated. For more details on this restriction, see IDKEY Column Values.

value

A scalar expression, or comma-separated list of scalar expressions, specified in the VALUES clause that supplies the data values for the corresponding columns in column. Specifying fewer values than columns generates an SQLCODE -62 error. Specifying more values than columns generates an SQLCODE -116 error.

INSERT OR UPDATE has the same value restrictions as INSERT. For more details, see the value argument of the INSERT command.

array

A dynamic local array of values specified as a host variable. This value applies to Embedded SQL only.

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, the result set of which supplies the data values for the corresponding columns specified in column.

The SELECT query extracts column data from one or more tables and the INSERT OR UPDATE command creates corresponding new rows in its table containing this column data. Corresponding columns can have different column names and column lengths, so long as the inserted data can fit in the table column. If the corresponding columns do not pass data type and length validation checks, InterSystems SQL generates an SQLCODE -104 error.

An INSERT OR UPDATE with SELECT operation sets the %ROWCOUNT variable to the number of rows inserted or updated (either 0 or a positive integer).

%keyword

Keyword options that configure INSERT OR UPDATE processing. You can specify keyword options in any order. Separate multiple keyword options by spaces.

You can specify these keywords:

• %NOCHECK — Disable unique value checking and foreign key referential integrity checking. Disables the update operation of INSERT OR UPDATE.

• %NOFPLAN — Ignore any frozen plans for this operation and generate a new query plan.

• %NOINDEX — Disable setting of index maps during INSERT OR UPDATE processing.

• %NOJOURN — Suppress journaling and turns off transactions for the duration of the insert operation.

• %NOLOCK — Disable locking of the row upon INSERT OR UPDATE.

• %NOTRIGGER — Do not pull base table insert triggers during INSERT OR UPDATE processing.

• %PROFILE, %PROFILE_ALL — Generate performance analysis statistics (SQLStats) for the INSERT OR UPDATE statement.

  • %PROFILE collects SQLStats for the main query module

  • %PROFILE_ALL collects SQLStats for the main query module and all of its subquery modules

For more details on these keywords, see the keyword argument of the INSERT command.

Insert or Update Rows in a Table

In this example, you create a new table (SQLUser.CaveDwellers), use INSERT to populate the table with data, and then use INSERT OR UPDATE to add additional rows and update existing rows.

Create a table with a column, Num, that is designated as the primary key. This constraint enforces column values to be unique and not null.

CREATE TABLE SQLUser.CaveDwellers (
Num INTEGER PRIMARY KEY,
CaveCluster CHAR(80) NOT NULL,
Troglodyte CHAR(50) NOT NULL)

Insert three rows into the table using an INSERT OR UPDATE statement, then use SELECT * to display the table data. Because the rows did not previously exist, INSERT OR UPDATE performs an insert operation for all of them.

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (1,'Bedrock','Flintstone,Fred')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (2,'Bedrock','Flintstone,Wilma')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (3,'Bedrock','Flintstone,Pebbles')

SELECT * FROM SQLUser.CaveDwellers

Insert or update four additional rows of data.

• For the first three statements, INSERT OR UPDATE performs an insert operation, because the values being inserted into the primary key column, Num, are not already in the table.

• For the last statements INSERT OR UPDATE performs an update operation, because the Num column value of 3 is already in the table. INSERT OR UPDATE updates the Troglodyte column with the new value for that row.

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (4,'Bedrock','Rubble,Barney')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (5,'Bedrock','Rubble,Betty')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (6,'Bedrock','Rubble,Bamm-Bamm')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (3,'Bedrock','Flintstone-Rubble,Pebbles')

SELECT * FROM SQLUser.CaveDwellers

Delete the table when you are done.

DROP TABLE SQLUser.CaveDwellers

Uniqueness Checks

INSERT OR UPDATE determines if a row exists by matching UNIQUE column values to the existing data values. If a UNIQUE constraint violation occurs, INSERT OR UPDATE performs an update operation. The UNIQUE column value can be explicitly specified in INSERT OR UPDATE or it can be the result of a column default value or a computed value.

When INSERT OR UPDATE is run against a sharded table, if the shard key is the same as or a subset of the UNIQUE KEY constraint, INSERT OR UPDATE performs an update operation.

If the INSERT OR UPDATE attempts to perform an update because of any other unique values found (that are not the shard key), the command fails with an SQLCODE -120 error due to the unique constraint failure.

Counter Columns

When an INSERT OR UPDATE is executed, InterSystems IRIS initially assumes the operation is an insert. Therefore, it increments by 1 the internal counters used to supply integers to SERIAL (%Library.CounterOpens in a new tab) columns. An insert uses these incremented counter values to assign integer values to these columns. If, however, InterSystems IRIS 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 columns. If the next operation is an insert, this results in a gap in the integer sequence for these columns. This is shown in the following example:

1. The internal counter value is 4. INSERT OR UPDATE increments the internal counter, then inserts Row 5: internal counter = 5, SERIAL column value = 5.

2. INSERT OR UPDATE increments the internal counter, then determines that it must perform an update on an existing row: internal counter = 6, no change to column counters.

3. INSERT OR UPDATE increments internal counter, then inserts a row: internal counter = 7, SERIAL column value = 7.

IDENTITY and RowID Columns

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

• If no IDENTITY column is defined for the table, an insert operation causes InterSystems IRIS to automatically assign the next sequential integer value to the ID (RowID) column. Update operations have no effect on subsequent inserts. Thus, INSERT OR UPDATE performs the same insert operation as INSERT.

• If an IDENTITY column is defined for the table, an INSERT OR UPDATE causes InterSystems IRIS to increment by 1 the internal counter used to supply integers to the IDENTITY column, before determining if the operation will be an insert or an update. An insert operation assigns this incremented counter value to the IDENTITY column. If, however, InterSystems IRIS 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 column. The RowID column value is taken from the IDENTITY column value, resulting in a gap in the assignment of ID (RowID) integer values.

IDKEY Column Values

When using INSERT OR UPDATE, you can only insert IDKEY column values, not update them. If the table has an IDKEY index and another UNIQUE constraint, INSERT OR UPDATE matches these columns 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 column values do not match the existing IDKEY column values, this update fails and generates an SQLCODE -107 error, because the update is attempting to modify the IDKEY columns.

Consider a table with columns A, B, C, and D. The table has a primary key of (A,B) in an environment where the primary key is the IDKEY, and a UNIQUE constraint on columns (C,D).

SET OPTION PKEY_IS_IDKEY = TRUE

CREATE TABLE ABCD (
A INTEGER,
B INTEGER,
C INTEGER,
D INTEGER,
CONSTRAINT AB PRIMARY KEY (A,B),
CONSTRAINT CD UNIQUE (C,D))

The table also has two rows of data:

INSERT INTO ABCD SET A=1, B=1, C=2, D=2

INSERT INTO ABCD SET A=1, B=2, C=3, D=4

Suppose you try to insert this value:

INSERT OR UPDATE ABCD (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 attempts to change the column A value from 1 to 2. Because you cannot change an IDKEY value, the update fails with an SQLCODE -107 error.

Reset your environment to the default settings, where the primary key is not the IDKEY.

SET OPTION PKEY_IS_IDKEY = FALSE