Updating segment data

You can change a field value to another value or remove the field value altogether.

To change the data in a segment, use the UPDATE statement. You can also use the UPDATE statement to remove a value from a field (without removing the row) by changing the field value to an empty string.

Example: The following statement updates the hospital name to 'MISSION CREEK' for hospital 'H001007'.
UPDATE HOSPITAL SET HOSPNAME = 'MISSION CREEK'
WHERE HOSPITAL.HOSPCODE = 'H001007'

The SET clause names the fields that you want to update and provides the values that you want to assign to those fields. You can replace a field value in the SET clause with any of the following items:

  • An expression, which can be any of the following items:
    • A constant
    • A parameter marker

Next, identify the rows to update:

  • To update a single row, use a WHERE clause that locates one, and only one, row.
  • To update several rows, use a WHERE clause that locates only the rows that you want to update.

If you omit the WHERE clause, IMS updates every row in the segment with the values that you supply. If IMS cannot find the row you want to identify, a SQLIMSCODE of100 will be returned to the application.

If the UPDATE is successful, SQLIMSERRD(3) in the SQLIMSCA contains the number of updated rows. This number includes only the number of updated rows in the segment that is specified in the UPDATE statement.

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.

If IMS finds an error while executing your UPDATE statement (for example, an update value that is too large for the field), it returns an error. Upon receiving the error, the application will have to decide on how to manage the rows that were already changed. It can either commit or rollback the changes.

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'