Chapter 56 SQL command INSERT OR UPDATE

Add a new row to the table or update an existing row in the table.

outline

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

parameter

• %Keyword - optional - one or more of the following keyword options, separated by spaces:% NOCHECK,% NOFPLAN,% NOINDEX,% nojournal,% NOLOCK,% NOTRIGGER,% PROFILE,% PROFILE_ALL.
• Table - name of the table or view on which to insert. This parameter can be a subquery. The INTO keyword is optional.
• Column - optional - column names corresponding to the order of the supplied list of values or a comma separated list of column names. If omitted, the value list is applied to all columns in column number order.
• Scalar expression - a scalar expression or comma separated list of scalar expressions that provide data values for the corresponding column fields.
• : array() - embedded SQL only - a dynamic local array specified as the value of a host variable. The lowest subscript level of the array must not be specified. Therefore: myupdates (),: myupdates (5,) and: myupdates(1,1,) are valid specifications.
• Query - a selection query whose result set provides data values for the corresponding column fields of one or more rows.

describe

The INSERT or UPDATE statement is an extension of the INSERT statement (it is very similar to the INSERT statement):

• If the specified record does not exist, INSERT or UPDATE performs INSERT.
• If the specified record already exists, INSERT or UPDATE performs the UPDATE. It updates the record with the specified field value. The specified data is updated even if it is the same as the existing data.

INSERT or UPDATE determines whether a record exists by matching the UNIQUE KEY field value with the existing data value. If the UNIQUE KEY constraint is violated, INSERT or UPDATE will perform the UPDATE operation. Note that the UNIQUE KEY field value may not be the value explicitly specified in INSERT or UPDATE; It may be the result of column defaults or calculated values. When you run INSERT or UPDATE on a slice table, INSERT or UPDATE performs an UPDATE operation if the slice key is the same as (or a subset of) the UNIQUE KEY constraint. If INSERT or UPDATE attempts to perform an UPDATE because it finds any other UNIQUE KEY value (not a slice key), the command fails with an SQLCODE-119 error due to the UNIQUE constraint failure.

Note: since the% NOCHECK keyword disables unique value checking, INSERT or update% NOCHECK always results in an INSERT operation. Therefore, do not specify% NOCHECK.

INSERT or UPDATE of a single record always sets the% ROWCOUNT variable to 1 and the% ROWID variable of the inserted or updated row to 1.

INSERT or UPDATE statements combined with SELECT statements can INSERT and / or UPDATE multiple table rows.

INSERT or UPDATE uses the same syntax and usually has the same functions and limitations as INSERT statements. Special considerations for inserting or updating are described here. Unless otherwise noted here, see INSERT for details.

jurisdiction

INSERT or UPDATE requires both INSERT and UPDATE permissions. These permissions must be owned as table level or column level permissions. For table level permissions:

• No matter what operation is actually performed, the user must have INSERT and UPDATE permissions on the specified table.
• If you use a SELECT query to insert or update data in another table, you must have SELECT permission on that table.

If the user is the owner (creator) of the table, the user is automatically granted all permissions on the table. Otherwise, the user must be granted permission on the table. Otherwise, SQLCODE-99 error will be caused because% msg user 'name' does not have privileges for this operation. You can determine whether the current user has the appropriate permissions by calling the% CHECKPRIV command. You can assign table permissions to users using the GRANT command.

IDKEY field

You can INSERT IDKEY field values, but you cannot UPDATE IDKEY field values. If the table has an IDKEY index and another unique key constraint, INSERT or UPDATE matches these fields to determine whether to perform INSERT or UPDATE. If another key constraint fails, force INSERT or UPDATE to perform the UPDATE instead of INSERT. However, if the specified IDKEY field value does not match the existing IDKEY field value, this UPDATE will fail and generate a SQLCODE-107 error because the UPDATE is trying to modify the IDKEY field.

For example, the table MyTest defines 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

INSERT or UPDATE ABC(A, B, C, D) values (2, 2, 3, 4) are called. Because the UNIQUE(C, D) constraint fails, the statement cannot execute INSERT. Instead, it tries to UPDATE line 2. The IDKEY in line 2 is (1, 2), so the INSERT or UPDATE statement will attempt to change the value of field A from 1 to 2. However, the IDKEY value cannot be changed, so the UPDATE fails with an SQLCODE-107 error.

Counter field

When you execute INSERT or UPDATE, IRIS initially assumes that the operation will be INSERT. Therefore, it increments the internal counter used to provide an integer to the serial (% Library.Counter) field by 1. INSERT uses these incremented counter values to assign integer values to these fields. However, if IRIS determines that the operation needs to be updated, INSERT or UPDATE has incremented the internal counters, but it does not assign these incremented integer values to the counter field. If the next operation is INSERT, it will cause gaps in the integer sequence of these fields. The following example shows this:

1. The internal counter value is 4. INSERT or UPDATE increments the internal counter and then inserts line 5: internal counter = 5, serial field value = 5.
2. INSERT or UPDATE increments the internal counter and then determines that it must perform an UPDATE on the existing row: international counter = 6 without changing the field counter.
3. INSERT or UPDATE increments the internal counter and then inserts a row: internal counter = 7, sequence field value = 7.

Identity and RowID fields

The effect of INSERT or UPDATE on RowId value allocation depends on whether there is an identification field:

• If no identity field is defined for the table, the INSERT operation causes IRIS to automatically assign the next consecutive integer value to the ID(RowID) field. The UPDATE operation has no effect on subsequent inserts. Therefore, INSERT or UPDATE performs the same INSERT operation as INSERT.
• If an Identity field is defined for a table, INSERT or UPDATE causes IRIS to increment the internal counter used to provide an integer to the Identity field by 1 before determining whether the operation is INSERT or UPDATE. The INSERT operation assigns the incremented counter value to the identification field. However, if IRIS determines that the INSERT or UPDATE operation needs to be updated, it has incremented the internal counters, but these incremented integer values are not allocated. If the next INSERT or UPDATE operation is INSERT, it will cause a gap in the integer sequence identifying the field. The RowID field value is taken from the Identity field value, resulting in a gap in the allocation of ID(RowID) integer values.

Example

The following five examples: create a new table (SQLUser.CaveDwell); Use INSERT or UPDATE to fill the table with data; Use INSERT or UPDATE to add new rows and UPDATE existing rows; Use SELECT * to display data; And delete the table.

The following example uses CREATE TABLE to create a table with unique fields (NUM):

ClassMethod InsertOrUpdate()
{
	&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 { 
		w !,"Table creation" 
	} elseif SQLCODE = -201 { 
		w !,"Table already exists" 
	} else { 
		w !,"SQL Table creation error code: ",SQLCODE
		q 
	}
}

The following example uses class definitions to define the same table and define unique keys for num:

Class User.CaveDwellers Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {yx}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = CaveDwellers ]
{

Property Num As %Library.Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ SqlColumnNumber = 2 ];

Property CaveCluster As %Library.String(MAXLEN = 80) [ Required, SqlColumnNumber = 3 ];

Property Troglodyte As %Library.String(MAXLEN = 50) [ Required, SqlColumnNumber = 4 ];

/// DDL Unique Key Specification
Index CAVEDWELLERSUNIQUE1 On Num [ SqlName = CAVEDWELLERS_UNIQUE1, Unique ];

/// DDL Primary Key Specification
Index CaveDwellerPK On Num [ PrimaryKey, Type = index, Unique ];

/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement.  Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];

}

SELECT * FROM SQLUser.CaveDwellers ORDER BY Num

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

ClassMethod InsertOrUpdate1()
{

	&sql(
		INSERT OR UPDATE INTO SQLUser.CaveDwellers 
		(
			Num, CaveCluster, Troglodyte
		) 
		VALUES 
		(
			3, 'Bedrock', 'Flintstone,Fred'
		)
	)
	if SQLCODE = 0 { 
		SET rcount=%ROWCOUNT 
	}
	&sql(
		INSERT OR UPDATE INTO SQLUser.CaveDwellers 
		(
			Num, CaveCluster, Troglodyte
		) 
		VALUES 
		(
			4, 'Bedrock1', 'Flintstone,Wilma'
		)
	)
	if SQLCODE = 0 { 
		s rcount = rcount + %ROWCOUNT 
		w !,rcount," records inserted/updated" 
	} else { 
		w !,"Insert/Update failed, SQLCODE=",SQLCODE 
	}
}

ClassMethod InsertOrUpdate2()
{

	n 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 {
		w !,"Insert/Update succeeded"
		w !,%ROWCOUNT," records inserted/updated"
		w !,"Row ID=",%ROWID 
	} else {
		w !,"Insert/Update failed, SQLCODE=",SQLCODE 
	}
}

The following example deletes the table:

ClassMethod InsertOrUpdate3()
{
	&sql(DROP TABLE SQLUser.CaveDwellers)
	if SQLCODE = 0 {
		w !,"Table deleted" 
	} elseif SQLCODE = -30 {
		w !,"Table does not exist"
	} else {
		w !,"Failed to delete table. SQLCODE=",SQLCODE 
	}
}