Relational Data Framework Reference › SQL Statements › UPDATE Statement—Insert Values

UPDATE Statement—Insert Values

The UPDATE statement lets you insert values into selected columns in a table. The WHERE clause identifies the rows in the table that you want to update. You then specify simple expressions in the SET clause to indicate how you want to update the identified column.

Consider the following information when you use the UPDATE statement:

• If you omit the WHERE clause, SQL updates the identified columns in all rows.
• You can use host variables for colname and tablename.
• If you invoke an UPDATE statement for a cursor operation, the statement must be issued from an AOF rule, an OPS/REXX program, a TSO/E REXX program, or a TSO CLIST. You cannot invoke SQL commands that perform cursor operations from a TSO terminal.

Note: If you are updating multiple columns, specify a SET clause with multiple colname operands and separate the operands with commas.

Use this syntax in an AOF rule or an OPS/REXX program:

ADDRESS SQL "UPDATE keywords"

{tablename}
{SET colname=string|:hostvar|NULL}
 [WHERE searchcriteria]
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[SYSPLEX]
[SUBSYS(subsystem)]

To invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST, use this syntax:

OPSQL UPDATE

{tablename}
{SET colname=string|:hostvar|NULL}
 [WHERE searchcriteria]

Specify these operands for the UPDATE statement:

tablename

Defines the name of the table you want to update.

colname

Defines the name of the column into which you are inserting data. You can specify the colname value as a string, a host variable name, or as NULL. To update multiple columns, include multiple colname clauses in your UPDATE statement, and separate them with commas.

searchcriteria

(Optional) For a searched operation, the criteria on a WHERE clause can be any valid search criteria.

Note: For more information about specifying search criteria, see Searched Operations in the chapter “Using the Relational Data Framework” in the User Guide.

(Optional) For a cursor operation, the criteria on a WHERE clause must include CURRENT OF cursorname, which causes the function to be performed on the current row being processed in the cursorname operation.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of these values:

ALL

Routes the SQL command to all active MSF-defined systems, including the local system.

EXT

Routes the SQL command to all remote, active MSF-defined systems.

sysnames

Routes the SQL command to the specified systems. You may specify from one to eight system names as the value of sysnames.

Note: For more information about cross-system SQL operations, see Notes on Performing Cross-system SQL Operations in the chapter “Using the Relational Data Framework” in the User Guide.

SYSWAIT

(Optional). Defines the number of seconds the SQL processor waits for output from a remote system.

Limits: 1-300 seconds

Note: Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.

OUTPUT or NOOUTPUT

(Optional) Indicates whether the command returns output to the external data queue. To return output, specify OUTPUT; otherwise, specify NOOUTPUT.

NOOUTPUT is implied when:

• The ADDRESS SQL command is issued in an AOF rule
• You specify more than one system name as the value of the SYSTEM(sysnames) keyword
• You specify SYSTEM(ALL) or SYSTEM(EXT)

SYSPLEX

(Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.

SUBSYS

(Optional) Performs SQL operations on other CA OPS/MVS subsystems that are in communication with the current one. Specify the following value:

subsystem

Routes the SQL command to the specified subsystem. You can use only one four-character name of the subsystem as follows: OPSx.

Examples: UPDATE

These examples illustrate the use of the UPDATE statement:

• Example 1-Using UPDATE in a Searched Operation

  Suppose that you want to alter the SYSTEMS table by changing the current state value for the CICS1 system to UP. To do this, you would use this SQL statement in an AOF rule:

  ADDRESS SQL
    "UPDATE SYSTEMS SET CURRENT_STATE = 'UP' WHERE NAME = 'CICS1'"
  

• Example 2-A Searched Operations That Omits the WHERE Clause

  You have one or more rules that execute when CA OPS/MVS starts up, and you want to set the current state of all systems to DOWN. You plan to decide later whether a specific system should be up or down. You could invoke the SQL statement from a rule as follows:

  ADDRESS SQL
    "UPDATE SYSTEMS SET CURRENT_STATE = 'DOWN'"
  

  The absence of the WHERE clause from this statement signals that you want to update the CURRENT_STATE column in all rows.

• Example 3-Using UPDATE in a Cursor Operation

  To set the value of the STATUS column to UP for the current row in a cursor operation called STATDOWN, you could include this SQL statement in a TSO CLIST:

  OPSQL UPDATE APPLICATIONS SET STATUS = 'UP'
    WHERE CURRENT OF STATDOWN