UPDATE

Function

Restrictions

Database-Specific Considerations

Syntax Description

Example

Hold Status

Structured Mode Syntax

Reporting Mode Syntax

Output of Program SUBEX1S

After entering and confirming name:


Natural for OpenVMS	Version 6.3.12 for OpenVMS	SEARCH CONTENTS \| PDF BOOKS \| HOME UP PREV NEXT

— Statements —

UPDATE [RECORD] [IN] [STATEMENT] [(r)]

This document covers the following topics:

Function
Restrictions
Database-Specific Considerations
Syntax Description
Example

For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

The UPDATE statement is used to update one or more fields of a record in a database. The record to be updated must have been previously selected with a FIND, GET or READ statement (or, for Adabas only, with a STORE statement).

The use of the UPDATE statement causes each record read for processing in the corresponding FIND or READ statement to be placed in exclusive hold.

For further information, see Record Hold Logic (in the Programming Guide).

The UPDATE statement

must not be entered on the same line as the statement used to select the record to be updated;
cannot be applied to Entire System Server views.

SQL	The `UPDATE` statement can be used to update a row in a database table. It corresponds with the SQL statement `UPDATE WHERE CURRENT OF CURSOR` (Positioned `UPDATE`), which means that only the row which was read last can be updated. With most SQL databases, a row that was read with a `FIND SORTED BY` or with a `READ LOGICAL` statement cannot be updated.
XML	The statement cannot be used with XML databases.

SQL

The UPDATE statement can be used to update a row in a database table. It corresponds with the SQL statement UPDATE WHERE CURRENT OF CURSOR (Positioned UPDATE), which means that only the row which was read last can be updated.

With most SQL databases, a row that was read with a FIND SORTED BY or with a READ LOGICAL statement cannot be updated.

XML

The statement cannot be used with XML databases.

Operand Definition Table:

Operand	Possible Structure					Possible Formats													Referencing Permitted	Dynamic Definition
`operand1`		S	A			A		N	P	I	F	B	D	T	L				no	no
`operand2`	C	S	A			A		N	P	I	F	B	D	T	L				yes	no

Syntax Element Description:

Syntax Element Description

Syntax Element	Description
`(r)`	Statement Reference: The notation `(r)` is used to indicate the statement in which the record to be modified was read. `r` may be specified as a source-code line number or as a statement label. If no reference is specified, the `UPDATE` statement will reference the innermost active `READ` or `FIND` processing loop. If no `READ` or `FIND` loop is active, it will reference the last preceding `GET` (or `STORE`) statement. Note: The `UPDATE` statement must be placed within the `READ` or `FIND` loop it references.
`USING SAME`	USING SAME Clause: This clause is not permitted if a `DEFINE DATA` statement is used, because in that case the `UPDATE` statement always refers to the entire view as defined in the `DEFINE DATA` statement. The layout of the record buffer or format buffer may be declared using the `OBTAIN` statement. `USING SAME` can be used in reporting mode to indicate that the same fields as read in the statement referenced by the `UPDATE` statement are to be used for the update function. In this case, the most recent value assigned to each database field will be used to update the field. If no new value has been assigned, the old value will be used. If the field to be updated is an array range of a multiple-value field or periodic group and you use a variable index for this array range, the latest range will be updated. This means that if the index variable is modified after the record has been read and before the `UPDATE USING SAME` (reporting mode) or `UPDATE` (structured mode) statement respectively is executed, the range updated will not be the same as the range read.
`SET/WITH operand1=operand2`	SET/WITH Clause: This clause can be used in reporting mode to specify the fields to be updated and the values to be used. This clause is not permitted if a `DEFINE DATA` statement is used, because in that case the `UPDATE` statement always refers to the entire view as defined in the `DEFINE DATA` statement.

(r)

Statement Reference:

The notation (r) is used to indicate the statement in which the record to be modified was read. r may be specified as a source-code line number or as a statement label.

If no reference is specified, the UPDATE statement will reference the innermost active READ or FIND processing loop. If no READ or FIND loop is active, it will reference the last preceding GET (or STORE) statement.

Note:
The UPDATE statement must be placed within the READ or FIND loop it references.

USING SAME

USING SAME Clause:

This clause is not permitted if a DEFINE DATA statement is used, because in that case the UPDATE statement always refers to the entire view as defined in the DEFINE DATA statement.

The layout of the record buffer or format buffer may be declared using the OBTAIN statement.

USING SAME can be used in reporting mode to indicate that the same fields as read in the statement referenced by the UPDATE statement are to be used for the update function. In this case, the most recent value assigned to each database field will be used to update the field. If no new value has been assigned, the old value will be used.

If the field to be updated is an array range of a multiple-value field or periodic group and you use a variable index for this array range, the latest range will be updated. This means that if the index variable is modified after the record has been read and before the UPDATE USING SAME (reporting mode) or UPDATE (structured mode) statement respectively is executed, the range updated will not be the same as the range read.

SET/WITH
                      operand1=operand2

SET/WITH Clause:

This clause can be used in reporting mode to specify the fields to be updated and the values to be used.

This clause is not permitted if a DEFINE DATA statement is used, because in that case the UPDATE statement always refers to the entire view as defined in the DEFINE DATA statement.

** Example 'UPDEX1S': UPDATE (structured mode)                          
**                                                                      
** CAUTION: Executing this example will modify the database records!    
************************************************************************
DEFINE DATA LOCAL                                                       
1 EMPLOY-VIEW VIEW OF EMPLOYEES                                         
  2 NAME                                                                
  2 FIRST-NAME                                                          
  2 CITY                                                                
*                                                                       
1 #NAME (A20)                                                           
END-DEFINE                                                              
*                                                                       
INPUT 'ENTER A NAME:' #NAME (AD=M)                                      
IF #NAME = ' '                                                          
  STOP                                                                  
END-IF                                                                  
*                                                                       
FIND EMPLOY-VIEW WITH NAME = #NAME                                      
  IF NO RECORDS FOUND                      
    REINPUT WITH 'NO RECORDS FOUND'  MARK 1
  END-NOREC                                
  INPUT 'NAME:      ' NAME (AD=O) /        
        'FIRST NAME:' FIRST-NAME (AD=M) /  
        'CITY:      ' CITY (AD=M)           
  UPDATE                                   
  END TRANSACTION                        
END-FIND                                   
*                                          
END

ENTER A NAME: BROWN

[RECORD] [IN] [STATEMENT] [(r)]

SAME [RECORD]

{operand1=operand2}

NAME: BROWN
FIRST NAME: KENNETH
CITY: DERBY

Equivalent reporting-mode example: UPDEX1R.

SEARCH CONTENTS | PDF BOOKS | HOME UP PREV NEXT