UPDATE (SQL)
Synopsis
UPDATE [%keyword] table-ref [[AS] t-alias]
SET column1 = scalar-expression1 {,column2 = scalar-expression2} ...
[FROM [optimize-option] select-table [[AS] t-alias] {, select-table2 [[AS] t-alias]} ]
[WHERE condition-expression]
UPDATE [%keyword] table-ref [[AS] t-alias]
[ (column1 {,column2} ...) ] VALUES (scalar-expression1 {,scalar-expression2} ...)
[FROM ... ] [WHERE ...]
UPDATE [%keyword] table-ref [[AS] t-alias]
VALUES :array()
[FROM ... ] [WHERE ...]
UPDATE [%keyword] table-ref [[AS] t-alias]
SET column1 = scalar-expression1{,column2 = scalar-expression2} ...
[WHERE CURRENT OF cursor]
UPDATE [%keyword] table-ref [[AS] t-alias] [ (column1 {,column2} ...) ]
VALUES (scalar-expression1 {,scalar-expression2} ...)
[WHERE CURRENT OF cursor]
UPDATE [%keyword] table-ref [[AS] t-alias]
VALUES :array()
[WHERE CURRENT OF cursor]
Description
An UPDATE command changes existing values for columns in a table. You can update data in a table directly, update through a view, or update using a subquery enclosed in parentheses. Updating through a view is subject to requirements and restrictions, as described in CREATE VIEW.
The UPDATE command provides one or more new column values to one or more existing base table rows that contain those columns. Assignment of data values to columns is done using a value-assignment-statement. By default, a value-assignment-statement updates all rows in the table.
More commonly, an UPDATE specifies the updating of a specific row (or rows) based on a condition-expression. By default, an UPDATE operation goes through all of the rows of a table and updates all rows that satisfy the condition-expression. If no rows satisfy the condition-expression, UPDATE completes successfully and sets SQLCODE=100 (No more data).
You can specify a WHERE clause or a WHERE CURRENT OF clause (but not both). If the WHERE CURRENT OF clause is used, UPDATE updates the record at the current position of the cursor. For details on positioned operations, see WHERE CURRENT OF.
The UPDATE operation sets the %ROWCOUNT local variable to the number of updated rows, and the %ROWID local variable to the RowID value of the last row updated.
By default, the UPDATE operation is an all-or-nothing event. Either all specified rows and columns are updated, or none are.
For more details regarding the different ways of assigning values to columns, refer to the Value Assignment section below.
INSERT OR UPDATE
The INSERT OR UPDATE statement is a variant of the INSERT statement that performs both insert and update operations. First it attempts to perform an insert operation. If the insert request fails due to a UNIQUE KEY violation (for the field(s) of some unique key, there exists a row that already has the same value(s) as the row specified for the insert), then it automatically turns into an update request for that row, and INSERT OR UPDATE uses the specified field values to update the existing row.
SQLCODE Errors
By default, a multi-row UPDATE is an atomic operation. If one or more rows cannot be updated, the UPDATE operation fails and no rows are updated. InterSystems IRIS sets the SQLCODE variable, which indicates the success or failure of the UPDATE; if the operation failed, IRIS also sets %msg.
To update a table, the update must meet all table, column name, and value requirements, as follows.
Tables:
-
The table must exist in the current (or specified) namespace. If the specified table cannot be located, InterSystems IRIS issues an SQLCODE -30 error.
-
The table cannot be defined as READONLYOpens in a new tab. Attempting to compile an UPDATE that references a read-only table results in an SQLCODE -115 error. Note that this error is issued at compile time, rather than occurring at execution time. See the description of READONLY objects in Other Options for Persistent Classes.
-
The table cannot be locked IN EXCLUSIVE MODE by another process. Attempting to update a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire lock for UPDATE of table 'Sample.Person' on row with RowID = '10'. Note that an SQLCODE -110 error occurs only when the UPDATE statement locates the first record to be updated, then cannot lock it within the timeout period.
-
If the UPDATE specifies a non-existent field, an SQLCODE -29 is issued. To list all of the field names defined for a specified table, refer to Column Names and Numbers. If the field exists but none of the field values fulfill the UPDATE command’s WHERE clause, no rows are affected and SQLCODE 100 (end of data) is issued.
-
In rare cases, UPDATE with %NOLOCK locates a row to be updated, but then the row is immediately deleted by another process; this situation results in an SQLCODE -109 error: Cannot find the row designated for UPDATE. The %msg for this error lists the table name and the RowID.
-
If updating a table through a view, the view cannot be defined as WITH READ ONLY. Attempting to do so results in an SQLCODE -35 error. If the view is based on a sharded table, you cannot UPDATE through a view defined WITH CHECK OPTION. Attempting to do so results in an SQLCODE -35 with the %msg INSERT/UPDATE/DELETE not allowed for view (sample.myview) based on sharded table with check option conditions. See the CREATE VIEW command for further details.
Column Names and Values:
-
The update cannot include duplicate field names. Attempting an update that specifies two fields with the same name results in an SQLCODE -377 error.
-
You cannot update a field that has been locked by another concurrent process. Attempting to do so results in an SQLCODE -110 error. This SQLCODE error can also occur if you are performing such a large number of updates that a <LOCKTABLEFULL> error occurs.
-
You cannot update integer counter fields. These fields are non-modifiable. Attempting to do so generates the following errors: RowID field (SQLCODE -107); IDENTITY field (SQLCODE -107); SERIAL (%Library.CounterOpens in a new tab) field (SQLCODE -105); ROWVERSION field (SQLCODE -138). The field values for these fields are system-generated and not user-modifiable. Even when the user can insert an initial value for a counter field, the user cannot update the value.
The one exception is when adding a SERIAL (%Library.CounterOpens in a new tab) field to a table that has existing data. Existing records will have NULL for this added counter field. In this case, you can use UPDATE to change a NULL to an integer value. See the ALTER TABLE command for further details.
-
You cannot update a shard key field. Attempting a update a field that is part of a shard key generates an SQLCODE -154 error.
-
You cannot update a field value if the update would violate the field’s uniqueness constraints. Attempting to update the value of a field (or group of fields) such that the update would violate a uniqueness constraint or a primary key constraint results in an SQLCODE -120 error. This error is returned if the field has a UNIQUE data constraint, or if the unique fields constraint has been applied to a group of fields. The SQLCODE -120 %msg string includes both the field and the value that violate the uniqueness constraint. For example <Table 'Sample.MyTable', Constraint 'MYTABLE_UNIQUE3', Field(s) FullName="Molly Bloom"; failed unique check> or <Table 'Sample.MyTable', Constraint 'MYTABLE_PKEY2', Field(s) FullName="Molly Bloom"; failed unique check>. For details on listing a table’s unique value and primary key field constraints and the naming of constraints, refer to Catalog Details: Constraints.
-
You cannot update a field value if the update specifies a value that is not listed in its VALUELIST parameter. A property of a persistent class defined with a VALUELIST parameter can only accept as a valid value one of the values listed in VALUELIST, or be provided with no value (NULL). VALUELIST valid values are case-sensitive. Attempting to update with a data value that doesn’t match the VALUELIST values results in an SQLCODE -105 field value failed validation error.
-
Numbers are inserted in canonical form, but can be specified with leading and trailing zeros and multiple leading signs. However, in SQL, two consecutive minus signs are parsed as a single-line comment indicator. Therefore, attempting to specify a number with two consecutive leading minus signs results in an SQLCODE -12 error.
-
When using a WHERE CURRENT OF clause, you cannot update a field using the current field value to generate an updated value. For example, SET Salary=Salary+100 or SET Name=UPPER(Name). Attempting to do so results in an SQLCODE -69 error: SET <field> = <value expression> not allowed with WHERE CURRENT OF <cursor>.
-
If updating one of the specified rows would violate foreign key referential integrity (and %NOCHECK is not specified), the UPDATE fails to update any rows and instead issues an SQLCODE -124 error. This does not apply if the foreign key was defined with the NOCHECK keyword.
-
You cannot update a non-stream field with stream data. This results in an SQLCODE -303 error, as described below.
-
Inserted data values must pass display to logical mode conversion. InterSystems SQL stores data in logical mode format. For some data types, the logical format might differ from the display format. For example, date data is stored as an integer count of days, time data is stored as a count of seconds from midnight, and %List data is stored as an encoded string. Other data types, such as strings and numbers, require no conversion. Attempting to insert a value in a format that cannot be converted to its logical storage value results in an error (SQLCODE -146 for dates, SQLCODE -147 for times). For more details on mode conversions, see Data Display Options.
Arguments
%keyword
An optional argument specifying one or more of the following keyword options, separated by spaces: %NOCHECK, %NOFPLAN, %NOINDEX, %NOJOURN, %NOLOCK, %NOTRIGGER, %PROFILE, %PROFILE_ALL.
table-ref
The name of an existing table where data is to be updated. You can also specify a view through which to perform the update on a table. You cannot specify a table-valued function or JOIN syntax in this argument.
A table name (or view name) can be qualified (schema.table), or unqualified (table). An unqualified name is matched to its schema using either a schema search path (if provided) or the default schema name.
AS t-alias
An optional alias for a table-ref (table or view) name. An alias must be a valid identifier. The AS keyword is optional
FROM select-table
An optional FROM clause used to specify the table or tables used to determine which rows are to be updated.
Multiple tables can be specified as a comma-separated list or associated with ANSI join keywords. Any combination of tables or views can be specified. If you specify a comma between two select-tables here, InterSystems IRIS performs a CROSS JOIN on the tables and retrieves data from the results table of the JOIN operation. If you specify ANSI join keywords between two select-tables here, InterSystems IRIS performs the specified join operation. For further details, refer to the JOIN page of this manual.
You can optionally specify one or more optimize-option keywords to optimize query execution. The available options are: %ALLINDEX, %FIRSTTABLE select-table, %FULL, %INORDER, %IGNOREINDICES, %NOFLATTEN, %NOMERGE, %NOSVSO, %NOTOPOPT, %NOUNIONOROPT, %PARALLEL, and %STARTTABLE. See the FROM clause for further details.
WHERE condition-expression
An optional argument that specifies one or more boolean predicates used to determine which rows are to be updated. If a WHERE clause (or a WHERE CURRENT OF clause) is not supplied, UPDATE updates all the rows in the table. See the WHERE clause for further details.
WHERE CURRENT OF cursor
An optional argument specifying that the UPDATE operation updates the record at the current position of cursor. You can specify a WHERE CURRENT OF clause or a WHERE clause, but not both. For further details, see WHERE CURRENT OF.
column
An optional argument specifying the name of an existing column. Multiple column names are specified as a comma-separated list. If omitted, all columns are updated.
scalar-expression
A column data value expressed as a scalar expression. Multiple data values are specified as a comma-separated list with each data value corresponding in sequence to a column.
:array()
Embedded SQL only — An 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.
Value Assignment
You can assign new values to specified columns in a variety of ways.
-
Using the SET keyword, specify one or more column = scalar-expression pairs as a comma-separated list. For example:
SET StatusDate='05/12/06',Status='Purged'
-
Using the VALUES keyword, specify a list of columns equated to a corresponding scalar-expressions list. For example:
(StatusDate,Status) VALUES ('05/12/06','Purged')
When assigning scalar-expression values to a column list, there must be a scalar-expression for each specified column.
-
Using the VALUES keyword without a column list, specify a list of scalar-expressions that implicitly correspond to the columns of the row in column order. The following example specifies all of the columns in the table, specifying a literal value to update the Address column:
VALUES (Name,DOB,'22 Main St. Anytown MA 12345',SSN)
When assigning values to an implicit column list, you must supply a value for every updateable field, in the order that the columns are defined in the DDL. (You do not specify the non-updateable RowID column.) These values can either be a literal to specify a new value, or the field name to specify the existing value. You cannot specify placeholder commas or omit trailing fields.
-
Using the VALUES keyword without a column list, specify a subscripted array in which the numeric subscripts correspond to the column numbers, including in your column count the non-updateable RowID as column number 1. For example:
VALUES :myarray()
This value assignment can only be performed from Embedded SQL using a host variable. Unlike all other value assignments, this usage allows you to delay specifying which columns are to be updated until runtime (by populating the array at runtime). All other types of update require that the columns to be updated must be specified at compile time. This syntax cannot be used with a linked table; attempting to do so results in an SQLCODE=-155 error. For further details, see Host Variable as a Subscripted Array.
For program examples demonstrating each of these types of UPDATE, refer to the Examples section below.
List Structures
InterSystems IRIS supports the list structure data type %List (data type class %Library.List). This is a compressed binary format, which does not map to a corresponding native data type for InterSystems SQL. It corresponds to data type VARBINARY with a default MAXLEN of 32749. For this reason, Dynamic SQL cannot use UPDATE or INSERT to set a property value of type %List. For further details, refer to Data Types.
Stream Values
You can update data values in a stream field as follows:
-
For any table: A string literal or a host variable containing a string literal, for example:
SET literal="update stream string value" //do the update; use a string &sql(UPDATE MyStreamTable SET MyStreamField = :literal WHERE %ID=21)
-
For a non-sharded table: An object reference (OREF) to a stream object. InterSystems IRIS opens this object and copies its contents, updating the stream field. For example:
SET oref=##class(%Stream.GlobalCharacter).%New() DO oref.Write("Update stream string value non-shard 1") //do the update; use an actual OREF &sql(UPDATE MyStreamTable SET MyStreamField = :oref WHERE %ID=22)
or a string version of an OREF of a stream, for example:
SET oref=##class(%Stream.GlobalCharacter).%New() DO oref.Write("Update stream string value non-shard 2") //next line converts OREF to a string OREF set string=oref_"" //do the update &sql(UPDATE MyStreamTable SET MyStreamField = :string WHERE %ID=23)
-
For a sharded table: An object ID (OID) using a temporary stream object stored in the ^IRIS.Stream.Shard global:
SET clob=##class(%Stream.GlobalCharacter).%New("Shard") DO clob.Write("Update sharded table stream string value") SET sc=clob.%Save() // Handle $$$ISERR(sc) set ClobOid=clob.%Oid() //do the update &sql(UPDATE MyStreamTable SET MyStreamField = :ClobOid WHERE %ID=24)
You cannot update a non-Stream field with the contents of a Stream field. This results in an SQLCODE -303 error: “No implicit conversion of Stream value to non-Stream field in UPDATE assignment is supported”. To update a string field with Stream data, you must first use the SUBSTRING function to convert the first n characters of the Stream data to a string, as shown in the following example:
UPDATE MyTable
SET MyStringField=SUBSTRING(MyStreamField,1,2000)
Computed Fields
A field defined with COMPUTECODE may recompute its value as part of the UPDATE operation, as follows:
-
COMPUTECODE: value is computed and stored upon INSERT, value is not changed upon UPDATE.
-
COMPUTECODE with COMPUTEONCHANGE: value is computed and stored upon INSERT, is recomputed and stored upon UPDATE.
-
COMPUTECODE with DEFAULT and COMPUTEONCHANGE: default value is stored upon INSERT, value is computed and stored upon UPDATE. If the compute code contains a programming error (for example, divide by zero), the UPDATE operation fails with an SQLCODE -415 error.
-
COMPUTECODE with CALCULATED or TRANSIENT: you cannot UPDATE a value for this field because no value is stored. The value is computed when queried. However, if you attempt to update a value in a calculated field, InterSystems IRIS performs validation on the supplied value and issues an error if the value is invalid. If the value is valid, InterSystems IRIS performs no update operation, issues no SQLCODE error, and increments ROWCOUNT.
A COMPUTEONCHANGE computed field is not recomputed when no actual update occurs: when the UPDATE operation new field value is the same as the prior field value.
In most cases, you define a computed field as read-only. This prevents an update operation directly changing a value that is intended to be the result of a computation involving other field values. In this case, attempting to use UPDATE to overwrite the value of a computed field results in an SQLCODE -138 error.
However, you may wish to revise a computed field value to reflect an update to one (or more) of its source field values. You can do this by using an update trigger that recomputes the computed field value after you have updated a specified source field. For example, an update to the Salary data field might trip a trigger that recalculates the Bonus computed field. This update trigger recalculates Bonus and completes successfully, even when Bonus is a read-only field. See the CREATE TRIGGER statement.
You can use the CREATE TABLE ON UPDATE keyword phrase to define a field that is set to a literal or a system variable (such as the current timestamp) when the record is updated.
For further details, refer to Computing a field value on INSERT or UPDATE.
%SerialObject Properties
When updating data in a %SerialObject, you must update the table (persistent class) that references the embedded %SerialObject; you cannot update a %SerialObject directly. From the referencing table, you can either:
-
Use the referencing field to update values for multiple %SerialObject properties as a %List structure. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country (in that order), you update SET PAddress=$LISTBUILD('123 Main St.','Newtown','USA') or (PAddress) VALUES ($LISTBUILD('123 Main St.','Newtown','USA')) or (PAddress) VALUES (:vallist). The %List must contain values for the properties of the serial object (or placeholder commas) in the order that these properties are specified in the serial object.
This type of update may not perform validation of %SerialObject property values. Therefore, it is strongly suggested that you use the $SYSTEM.SQL.Schema.ValidateTable() method to perform Table Data Validation after updating %SerialObject property values using a %List structure.
-
Use underscore syntax to update values for individual %SerialObject properties in any order. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country, you update SET PAddress_City='Newtown',PAddress_Street='123 Main St.',PAddress_Country='USA'.
This type of update performs validation of %SerialObject property values.
FROM Clause
An UPDATE command may have no FROM keyword. It may simply specify the table (or view) to update, and select which rows to update using a WHERE clause.
However, you can also include an optional FROM clause after the value-assignment-statement. This FROM clause specifies one or more tables used to determine which records are to be updated. The FROM clause is commonly, but not always, used with a WHERE clause involving multiple tables. A FROM clause can be complex, and can include ANSI join syntax. Any syntax supported in a SELECT FROM clause is permitted in an UPDATE FROM clause. This UPDATE FROM clause provides functionality compatibility with Transact-SQL.
The following example shows how this FROM clauses might be used. It updates those records from the Employees table where the same EmpId is also found in the Retirees table:
UPDATE Employees AS Emp
SET retired='Yes'
FROM Retirees AS Rt
WHERE Emp.EmpId = Rt.EmpId
If the UPDATE table-ref and the FROM clause make reference to the same table, these references may either be to the same table, or to a join of two instances of the table. This depends on how table aliases are used:
-
If neither table reference has an alias, both reference the same table:
UPDATE table1 value-assignment FROM table1,table2 /* join of 2 tables */
-
If both table references have the same alias, both reference the same table:
UPDATE table1 AS x value-assignment FROM table1 AS x,table2 /* join of 2 tables */
-
If both table references have aliases, and the aliases are different, InterSystems IRIS performs a join of two instances of the table:
UPDATE table1 AS x value-assignment FROM table1 AS y,table2 /* join of 3 tables */
-
If the first table reference has an alias, and the second does not, InterSystems IRIS performs a join of two instances of the table:
UPDATE table1 AS x value-assignment FROM table1,table2 /* join of 3 tables */
-
If the first table reference does not have an alias, and the second has a single reference to the table with an alias, both reference the same table, and this table has the specified alias:
UPDATE table1 value-assignment FROM table1 AS x,table2 /* join of 2 tables */
-
If the first table reference does not have an alias, and the second has more than one reference to the table, InterSystems IRIS considers each aliased instance a separate table and performs a join on these tables:
UPDATE table1 value-assignment FROM table1,table1 AS x,table2 /* join of 3 tables */ UPDATE table1 value-assignment FROM table1 AS x,table1 AS y,table2 /* join of 4 tables */
%Keyword Arguments
Specifying %keyword argument(s) restricts processing as follows:
-
%NOCHECK — Unique value checking and foreign key referential integrity checking are not performed. Column data validation for data type, maximum length, data constraints, and other validation criteria is also not performed. The WITH CHECK OPTION validation for a view is not performed when performing an UPDATE through a view.
Note:Because use of %NOCHECK can result in invalid data, this %keyword argument should only be used when performing bulk inserts or updates from a reliable data source.
The user must have the corresponding %NOCHECK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOCHECK privileges.
If you wish to prevent updates that result in non-unique data values when specifying %NOCHECK, perform an EXISTS check prior to UPDATE.
If you wish to disable only foreign key referential integrity checking, use the $SYSTEM.SQL.Util.SetOption("FilerRefIntegrity")Opens in a new tab method rather than specifying %NOCHECK. Alternatively, a foreign key can be defined with the NOCHECK keyword, so that foreign key referential integrity checking is never performed.
-
%NOFPLAN — FROM clause syntax only: the frozen plan (if any) is ignored for this operation; the operation generates a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans.
-
%NOINDEX — the index maps are not set during UPDATE processing. The user must have the corresponding %NOINDEX administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE -99 error.
-
%NOJOURN — suppress journaling and disable transactions for the duration of the update operation. None of the changes made in any of the rows are journaled, including any triggers pulled. However, updates are still journaled in a mirrored environment. If you perform a ROLLBACK after a statement with %NOJOURN, the changes made by the statement will not be rolled back. The user must have the corresponding %NOJOURN administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE -99 error
-
%NOLOCK — the row is not locked upon UPDATE. This should only be used when a single user/process is updating the database. The user must have the corresponding %NOLOCK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE -99 error.
-
%NOTRIGGER — the base table triggers are not pulled during UPDATE processing. Neither BEFORE nor AFTER triggers are executed. The user must have the corresponding %NOTRIGGER administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE -99 error.
-
%PROFILE or %PROFILE_ALL — if one of these keyword directives is specified, SQLStats collecting code is generated. This is the same code that would be generated with PTools turned ON. The difference is that SQLStats collecting code is only generated for this specific statement. All other SQL statements within the routine/class being compiled will generate code as if PTools is turned OFF. This enables the user to profile/inspect specific problem SQL statements within an application without collecting irrelevant statistics for SQL statements that are not being investigated. For further details, refer to SQL Runtime Statistics.
%PROFILE collects SQLStats for the main query module. %PROFILE_ALL collects SQLStats for the main query module and all of its subquery modules.
You can specify multiple %keyword arguments in any order. Multiple arguments are separated by spaces.
Referential Integrity
If you do not specify %NOCHECK, InterSystems IRIS uses the system-wide configuration setting to determine whether to perform foreign key referential integrity checking; the default is to perform foreign key referential integrity checking. You can set this default system-wide, as described in Foreign Key Referential Integrity Checking. To determine the current system-wide setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.
This setting does not apply to foreign keys that have been defined with the NOCHECK keyword.
During an UPDATE operation, for every foreign key reference which has a field value being updated, a shared lock is acquired on both the old (pre-update) referenced row and the new (post-update) referenced row in the referenced table(s). These rows are locked while performing referential integrity checking and updating the row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced row is not changed between the referential integrity check and the completion of the update operation. Locking the old row ensures that the referenced row is not changed before a potential rollback of the UPDATE. Locking the new row ensures that the referenced row is not changed between the referential integrity checking and the completion of the update operation.
If an UPDATE operation with %NOLOCK is performed on a foreign key field defined with CASCADE, SET NULL, or SET DEFAULT, the corresponding referential action changing the foreign key table is also performed with %NOLOCK.
Atomicity
By default, UPDATE, INSERT, DELETE, and TRUNCATE TABLE are atomic operations. An UPDATE either completes successfully or the whole operation is rolled back. If any of the specified rows cannot be updated, none of the specified rows are updated and the database reverts to its state before issuing the UPDATE.
You can modify this default for the current process within SQL by invoking SET TRANSACTION %COMMITMODE. You can modify this default for the current process in ObjectScript by invoking the SetOption()Opens in a new tab method, as follows SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). The following intval integer options are available:
-
1 or IMPLICIT (autocommit on) — The default behavior, as described above. Each UPDATE constitutes a separate transaction.
-
2 or EXPLICIT (autocommit off) — If no transaction is in progress, an UPDATE automatically initiates a transaction, but you must explicitly COMMIT or ROLLBACK to end the transaction. In EXPLICIT mode the number of database operations per transaction is user-defined.
-
0 or NONE (no auto transaction) — No transaction is initiated when you invoke UPDATE. A failed UPDATE operation can leave the database in an inconsistent state, with some of the specified rows updated and some not updated. To provide transaction support in this mode you must use START TRANSACTION to initiate the transaction and COMMIT or ROLLBACK to end the transaction.
A sharded table is always in no auto transaction mode, which means all inserts, updates, and deletes to sharded tables are performed outside the scope of a transaction.
You can determine the atomicity setting for the current process using the GetOption("AutoCommit")Opens in a new tab method, as shown in the following ObjectScript example:
SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
IF x=1 {
WRITE "Default atomicity behavior",!
WRITE "automatic commit or rollback" }
ELSEIF x=0 {
WRITE "No transaction initiated, no atomicity:",!
WRITE "failed DELETE can leave database inconsistent",!
WRITE "rollback is not supported" }
ELSE { WRITE "Explicit commit or rollback required" }
Transaction Locking
If you do not specify %NOLOCK, the system automatically performs standard record locking on INSERT, UPDATE, and DELETE operations. Each affected record (row) is locked for the duration of the current transaction.
The default lock threshold is 1000 locks per table. This means that if you update more than 1000 records from a table during a transaction, the lock threshold is reached and InterSystems IRIS automatically escalates the locking level from record locks to a table lock. This permits large-scale updates during a transaction without overflowing the lock table.
InterSystems IRIS applies one of the two following lock escalation strategies:
-
“E”-type lock escalation: InterSystems IRIS uses this type of lock escalation if the following are true: (1) the class uses %Storage.Persistent (you can determine this from the Catalog Details in the Management Portal SQL schema display). (2) the class either does not specify an IDKey index, or specifies a single-property IDKey index. “E”-type lock escalation is described in the LOCK command in the ObjectScript Reference.
-
Traditional SQL lock escalation: The most likely reason why a class would not use “E”-type lock escalation is the presence of a multi-property IDKey index. In this case, each %Save increments the lock counter. This means if you do 1001 saves of a single object within a transaction, InterSystems IRIS will attempt to escalate the lock.
For both lock escalation strategies, you can determine the current system-wide lock threshold value using the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method. The default is 1000. This system-wide lock threshold value is configurable:
-
Using the $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab method.
-
Using the Management Portal. Go to System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Lock escalation threshold. The default is 1000 locks. If you change this setting, any new process started after changing it will have the new setting.
You must have USE permission on the %Admin Manage Resource to change the lock threshold. InterSystems IRIS immediately applies any change made to the lock threshold value to all current processes.
One potential consequence of automatic lock escalation is a deadlock situation that might occur when an attempt to escalate to a table lock conflicts with another process holding a record lock in that table. There are several possible strategies to avoid this: (1) increase the lock escalation threshold so that lock escalation is unlikely to occur within a transaction. (2) substantially lower the lock escalation threshold so that lock escalation occurs almost immediately, thus decreasing the opportunity for other processes to lock a record in the same table. (3) apply a table lock for the duration of the transaction and do not perform record locks. This can be done at the start of the transaction by specifying LOCK TABLE, then UNLOCK TABLE (without the IMMEDIATE keyword, so that the table lock persists until the end of the transaction), then perform updates with the %NOLOCK option.
Automatic lock escalation is intended to prevent overflow of the lock table. However, if you perform such a large number of updates that a <LOCKTABLEFULL> error occurs, UPDATE issues an SQLCODE -110 error.
For further details on transaction locking refer to Transaction Processing.
Counter Incrementing
ROWVERSION Counter Increment
If a table has a field of data type ROWVERSION, performing an update on a row automatically updates the integer value of this field. The ROWVERSION field takes the next sequential integer from the namespace-wide row version counter. Attempting to specify an update value to a ROWVERSION field results in an SQLCODE -138 error.
SERIAL (%Counter) Counter Increment
An UPDATE operation has no effect on SERIAL (%Library.Counter) counter field values. However, an update performed using INSERT OR UPDATE causes a skip in integer sequence for subsequent insert operations for a SERIAL field. Refer to INSERT OR UPDATE for further details.
Privileges
To perform an update, you must either have table-level UPDATE privilege for the specified table (or view) or column-level UPDATE privilege for the specified column(s). When updating all fields in a row, note that column-level privileges cover all table columns named in the GRANT command; table-level privileges cover all table columns, including those added after the privilege was assigned.
-
The user must have UPDATE privilege on the specified table, or column-level UPDATE privilege for all columns in the update field list.
-
The user must have SELECT privilege for fields in a WHERE clause, whether or not those fields are to be updated. You must have both SELECT and UPDATE privileges for those fields if they are included in the update field list. In the following example, the Name field must have (at least) column-level SELECT privilege:
UPDATE Sample.Employee (Salary) VALUES (1000000) WHERE Name='Smith, John'
In the above example, the Salary field requires only column-level UPDATE privilege.
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.
When a property is defined as ReadOnly, the corresponding table field is also defined as ReadOnly. A ReadOnly field may only be assigned a value using InitialExpression or SqlComputed. Attempting to update a value (even a NULL value) for a field for which you have column-level ReadOnly (SELECT or REFERENCES) privilege results in an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field. When you link a table using the Link Table Wizard, you have the option of defining fields as Read Only. The field on the source system might not be read only, but if InterSystems IRIS defines the linked table's field as Read Only, attempting an UPDATE that references this field results in an SQLCODE -138 error.
Row-Level Security
InterSystems IRIS row-level security permits UPDATE to modify any row that security permits it to access. It allows you to update a row even if the update creates a row that security will not permit you to subsequently access. To ensure that an update does not prevent you from subsequent SELECT access to the row, it is recommended that you perform the UPDATE through a view that has a WITH CHECK OPTION. For further details, refer to CREATE VIEW.
Examples
The examples in this section update the SQLUser.MyStudents table. The following example creates the SQLUser.MyStudents table and populates it with data. Because repeated execution of this example would accumulate records with duplicate data, it uses TRUNCATE TABLE to remove old data before invoking INSERT. Execute this example before invoking the UPDATE examples:
CreateStudentTable
SET stuDDL=5
SET stuDDL(1)="CREATE TABLE SQLUser.MyStudents ("
SET stuDDL(2)="StudentName VARCHAR(32),StudentDOB DATE,"
SET stuDDL(3)="StudentAge INTEGER COMPUTECODE {SET {StudentAge}="
SET stuDDL(4)="$PIECE(($PIECE($H,"","",1)-{StudentDOB})/365,""."",1)} CALCULATED,"
SET stuDDL(5)="Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))"
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.stuDDL)
IF qStatus'=1 {WRITE "DDL %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rtn = tStatement.%Execute()
IF rtn.%SQLCODE=0 {WRITE !,"Table Create successful"}
ELSEIF rtn.%SQLCODE=-201 {WRITE "Table already exists, SQLCODE=",rtn.%SQLCODE,!}
ELSE {WRITE !,"table create failed, SQLCODE=",rtn.%SQLCODE,!
WRITE rtn.%Message,! }
RemoveOldData
SET clearit="TRUNCATE TABLE SQLUser.MyStudents"
SET qStatus = tStatement.%Prepare(clearit)
IF qStatus'=1 {WRITE "Truncate %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET truncrtn = tStatement.%Execute()
IF truncrtn.%SQLCODE=0 {WRITE !,"Table old data removed",!}
ELSEIF truncrtn.%SQLCODE=100 {WRITE !,"no data to be removed",!}
ELSE {WRITE !,"truncate failed, SQLCODE=",truncrtn.%SQLCODE," ",truncrtn.%Message,! }
PopulateStudentTable
SET studentpop=2
SET studentpop(1)="INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "
SET studentpop(2)="SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
SET qStatus = tStatement.%Prepare(.studentpop)
IF qStatus'=1 {WRITE "Populate %Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET poprtn = tStatement.%Execute()
IF poprtn.%SQLCODE=0 {WRITE !,"Table Populate successful",!
WRITE poprtn.%ROWCOUNT," rows inserted"}
ELSE {WRITE !,"table populate failed, SQLCODE=",poprtn.%SQLCODE,!
WRITE poprtn.%Message }
You can use the following query to display the results of these examples:
SELECT %ID,* FROM SQLUser.MyStudents ORDER BY StudentAge,%ID
Some of the following UPDATE examples depend on field values set by other UPDATE examples; they should be run in the order specified.
In the following Dynamic SQL example, a SET field=value UPDATE modifies a specified field in selected records. In the MyStudents table, children under the age of 7 are not given grades:
SET studentupdate=3
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="SET FinalGrade='NA' "
SET studentupdate(3)="WHERE StudentAge <= 6"
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message }
In the following cursor-based Embedded SQL example, a SET field1=value1,field2=value2 UPDATE modifies several fields in selected records. In the MyStudents table, it updates specified student records with Q1 and Q2 grades:
#sqlcompile path=Sample
NEW %ROWCOUNT,%ROWID
&sql(DECLARE StuCursor CURSOR FOR
SELECT * FROM MyStudents
WHERE %ID IN(10,12,14,16,18,20,22,24) AND StudentAge > 6)
&sql(OPEN StuCursor)
QUIT:(SQLCODE'=0)
FOR { &sql(FETCH StuCursor)
QUIT:SQLCODE
&sql(Update MyStudents SET Q1Grade='A',Q2Grade='A'
WHERE CURRENT OF StuCursor)
IF SQLCODE=0 {
WRITE !,"Table Update successful"
WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
ELSE {
WRITE !,"Table Update failed, SQLCODE=",SQLCODE }
}
&sql(CLOSE StuCursor)
In the following Dynamic SQL example, a field-list VALUES value-list UPDATE modifies the values of several fields in selected records. In the MyStudents table, children who don’t receive a final grade also don’t receive quarterly grades:
SET studentupdate=3
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="(Q1Grade,Q2Grade,Q3Grade) VALUES ('x','x','x') "
SET studentupdate(3)="WHERE FinalGrade='NA'"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Dynamic SQL example, a VALUES value-list UPDATE modifies all the field values in selected records. Note that this syntax requires that you specify a value for every field in the record. In the MyStudents table, several children have been withdrawn from school. Their record IDs and names are retained, with the word WITHDRAWN appended to the name; all other data is removed and the DOB field is used for the withdrawal date:
SET studentupdate=4
SET studentupdate(1)="UPDATE SQLUser.MyStudents "
SET studentupdate(2)="VALUES (StudentName||' WITHDRAWN',"
SET studentupdate(3)="$PIECE($HOROLOG,',',1),00,'-','-','-','XX') "
SET studentupdate(4)="WHERE %ID IN(7,10,22)"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Dynamic SQL example, a subquery UPDATE uses a subquery to select records. It then modifies these records using SET field=value syntax. Because of the way that StudentAge is calculated from date of birth in SQLUser.MyStudents, anyone less than a year old has a calculated age of <Null>, and anyone whose date of birth has been nulled has a very high calculated age. Here the StudentName field is flagged for future confirmation of the date of birth:
SET studentupdate=3
SET studentupdate(1)="UPDATE (SELECT StudentName FROM SQLUser.MyStudents "
SET studentupdate(2)="WHERE StudentAge IS NULL OR StudentAge > 21) "
SET studentupdate(3)="SET StudentName = StudentName||' *** CHECK DOB' "
SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
SET qStatus = tStatement.%Prepare(.studentupdate)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET uprtn = tStatement.%Execute()
IF uprtn.%SQLCODE=0 {WRITE !,"Table Update successful"
WRITE !,"Rows updated=",uprtn.%ROWCOUNT," Final RowID=",uprtn.%ROWID}
ELSE {WRITE !,"Table Update failed, SQLCODE=",uprtn.%SQLCODE," ",uprtn.%Message,! }
In the following Embedded SQL example, a VALUES :array() UPDATE modifies the field values specified by column number in the array in selected records. A VALUES :array() update can only be done in Embedded SQL. Note that this syntax requires that you specify each value by DDL column number (including in your column count the RowID column (column 1) but supplying no value to this non-modifiable field). In the MyStudents table, children between 4 and 6 (inclusive) are given a ‘P’ (for ‘Present’) in their Q1Grade (column 5) and Q2Grade (column 6) fields. All other record data remains unchanged:
SET arry(5)="P"
SET arry(6)="P"
&sql(UPDATE SQLUser.MyStudents VALUES :arry()
WHERE FinalGrade='NA' AND StudentAge > 3)
IF SQLCODE=0 {WRITE "Table Update successful",!
WRITE "Rows updated=",%ROWCOUNT," Final RowID=",%ROWID }
ELSE {WRITE "Table Update failed, SQLCODE=",SQLCODE,! }