IBM Tivoli Netcool/OMNIbus, Version 7.4

Updating the data in table columns (UPDATE command)

Use the UPDATE command to update one or more columns in an existing row of data in a table.

Syntax

UPDATE [database_name.]object_name
 [ VIA value_of_primary_key_column,... ]
 SET column_name = expression,...
 [ WHERE condition ];

You can use column values in calculations. In the following example, Severity is set to 0 when an alert has been acknowledged:

update status set Severity=(1-Acknowledged)*Severity;

You cannot update system-maintained columns such as Serial, or columns where the NOMODIFY property is set to TRUE. When the NOMODIFY property is set to TRUE, the value of a column cannot be changed after the initial INSERT command.

For other columns, or when the NOMODIFY property is set to FALSE, you can update rows in a table using the WHERE clause or the VIA clause.

Using the WHERE clause

If you include a WHERE clause, only rows meeting the criteria specified in the condition are updated. If no condition is specified in the WHERE clause, all rows are updated.

The WHERE clause can perform the same functions that the VIA clause does in earlier versions of Tivoli Netcool/OMNIbus. If the primary key is specified in the WHERE clause, the ObjectServer will not perform a full table scan but will instead directly access rows using the primary key. The following examples show some uses of the WHERE clause.

To set the Severity to 0 for rows of the alerts.status table where the Node is equal to Fred, enter:

update alerts.status set Severity = 0 where Node = 'Fred';

To search for rows where the Severity is equal to 1 and the Node is equal to Fred, and then set the Severity to 0 and change the Summary field to the string "Discarded", enter:

update alerts.status set Severity = 0, Summary = 'Discarded'
where Severity = 1 and Node = 'Fred';

Using the VIA clause

The VIA clause was deprecated with Tivoli Netcool/OMNIbus V7.2 but it is still valid. Its functions can now be performed by the WHERE clause.

If you know the value of the primary key for the row that you want to update, you can specify the value using the VIA clause. If there is more than one primary key column, the values must be specified in order and separated by commas (,). String values must be enclosed in single quotes (’).

If multiple rows are being updated, the primary key(s) for each row must be enclosed in square brackets ([ ]) and separated by commas (,).

The VIA clause can be useful for documenting your SQL. For example, it can be used to let the maintainer of triggers know that the UPDATE is being performed using primary keys.

The following example shows how to update multiple primary keys using the UPDATE VIA command:

update alerts.status via ['722Identifier722'],['294Identifier294'] set Severity = 3;

Given a table with the following schema, Example 1 shows how to update a single row in a table with multiple primary keys and Example 2 shows how to update multiple rows (three in this case) in a table with multiple primary keys.

Example schema:

create table alerts.example persistent ( col1 int primary key, col2 int primary key, 
col3 int primary key, col4 int );

Example 1:

update alerts.example via 1,1,1 SET col4 = 10;

Example 2:

update alerts.example via [1,1,1],[2,2,2],[3,3,3] SET col4 = 33;