UPDATE

The UPDATE statement updates the values of specified columns in rows of a table.

The searched UPDATE form is used to update one or more rows optionally determined by a search condition.

Invocation

This statement can be embedded in a COBOL or Java application program or issued interactively. An UPDATE can be embedded in an application program. It is an executable statement that can be dynamically prepared.

Syntax for COBOL

Figure 0. update
Read syntax diagramSkip visual syntax diagram UPDATE table SET assignment-clause WHEREsearch-condition
table
Read syntax diagramSkip visual syntax diagramschema-name.table-name
assignment clause
Read syntax diagramSkip visual syntax diagram,column=value
column
Read syntax diagramSkip visual syntax diagramtable-name. column-name

Syntax for Java

Figure 0. update
Read syntax diagramSkip visual syntax diagram UPDATE table SET assignment-clause WHEREsearch-condition
table
Read syntax diagramSkip visual syntax diagramschema-name.table-name
assignment clause
Read syntax diagramSkip visual syntax diagram,column=value
column
Read syntax diagramSkip visual syntax diagramschema-name.table-name.column-name1
Notes:
  • 1 You can have the same column name in multiple tables, but if the table is not qualified, each table must be searched for the column.

Description

The following keyword parameters are defined for the UPDATE statement:
UPDATE
Identifies the object of the UPDATE statement.
table-name
The table-name defines the name of the table in your SQL query. The name must identify a segment in IMS.
schema-name
The schema-name defines the schema in your SQL query. In IMS, the schema name is the PCB name.
SET
Introduces the assignment of values to column names.
column-name
Identifies a column that is to be updated. column-name must identify a field of the specified segment.
WHERE
Specifies the rows to be updated. You can omit the clause or give a search condition. When the clause is omitted, all the rows of the table are updated.
search-condition
Is any search condition as described in Search conditions. Each column-name in the search condition must identify a column of the table.

The search condition is applied to each row of the table and the rows are those for which the result of the search condition is true are updated.

value
Indicates the new value of the column.
schema-name
The schema-name defines the schema in your SQL query. In IMS, the schema name is the PCB name.
table-name
The table-name defines the name of the table in your SQL query.
column-name
The column-name defines the name of the column in your SQL query.

Notes

Update rules:
Update values must satisfy the following rules. If they do not, or if other errors occur during the execution of the UPDATE statement, no rows are updated and the position of the cursors are not changed.
  • Assignment. Update values are assigned to columns using the assignment rules described in Language elements.
  • When updating a record in a table at a non-root level, you must specify values for all the foreign key fields of the table to identify the exact record (or segment instance) to update.
  • Making an UPDATE on a foreign key field is invalid.
Number of rows updated:
For COBOL, the value of SQLIMSERRD(3) in the SQLIMSCA is the number of rows updated after an UPDATE statement completes execution. For a complete description of the SQLIMSCA, including exceptions to the preceding sentence, see SQL communication area (SQLIMSCA).

Examples

Updating one column in a record
The following statement updates the root:
UPDATE HOSPITAL SET HOSPNAME = 'MISSION CREEK'
WHERE HOSPITAL.HOSPCODE = 'H001007'
Updating multiple fields in a specified record in a hierarchic path
Foreign keys are used to maintain referential integrity by identifying the exact record (or segment instance) to update. The following statement updates a WARD record under a specific HOSPITAL. In this example, the WARD table has the virtual foreign key HOSPITAL_HOSPCODE. The record will be updated if and only if there is a HOSPCODE in the HOSPITAL table with the value of 'H5140070000H'.
UPDATE WARD SET WARDNAME = 'EMGY', 
   DOCCOUNT = '2', NURCOUNT = '4' 
WHERE HOSPITAL_HOSPCODE = 'H5140070000H' 
   AND WARDNO = '01'
Example of an invalid UPDATE query
This statement is invalid because it does not use the correct syntax to specify a legal value for the virtual foreign key field (HOSPITAL_HOSPCODE).
UPDATE WARD SET WARDNAME = 'EMGY', 
   DOCCOUNT = '2', NURCOUNT = '4' 
WHERE HOSPITAL.HOSPCODE = 'H5140070000H' 
   AND WARDNO = '01'
Example of an invalid foreign key field UPDATE query
Making an UPDATE query on a foreign key field is invalid. For example, the following UPDATE query will fail:
UPDATE WARD SET WARDNAME = 'EMGY', 
   HOSPITAL_HOSPCODE = 'H5140070000H' 
WHERE WARDNO = '01'