UPDATE statement [SQL Remote]

Modifies data in the database.

Syntax

Single-row updates of a single table executed by the Message Agent

UPDATE table-name
SET column-name = expression, ...
[ VERIFY ( column-name, ... ) VALUES ( expression, ... ) ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ], ... ]
[ OPTION( query-hint, ... ) ]

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| FORCE NO OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value :
hostvar (indicator allowed)
| string
| identifier
| number

Implement a specific SQL Remote feature

UPDATE table-name
PUBLICATION publication-name
{ SUBSCRIBE BY subscription-expression |
   OLD SUBSCRIBE BY old-subscription-expression
   NEW SUBSCRIBE BY new-subscription-expression }
WHERE search-condition

expression : value | subquery

Parameters

Syntax - Implement a specific SQL Remote feature
- table-name
  The table-name indicates the base table that must be modified on the remote databases.
- publication-name
  The publication-name indicates the publication for which subscriptions must be changed.
- subscription-expression
  The value of subscription-expression is used by SQL Remote to determine both new and existing recipients of the rows. The subscription-expression is either a value or a subquery. Alternatively, you can provide both OLD and NEW subscription expressions.
- WHERE clause
  The WHERE clause specifies which rows are to be transferred between subscribed databases.

Remarks

Syntax - Single-row updates of a single table executed by the Message Agent
The SQL Remote Message Agent uses this syntax when applying messages to the database. All of the parameters and remarks about updating rows on a single table in the standard UPDATE statement also apply to this variation of the UPDATE statement.

The VERIFY clause contains a set of values that are expected to be present in the row being updated. If the values do not match, any RESOLVE UPDATE triggers are fired before the UPDATE proceeds. The UPDATE does not fail if the VERIFY clause fails to match. When the VERIFY clause is specified, only one table can be updated at a time.
Syntax - Implement a specific SQL Remote feature
This syntax is for modifying a row in one table that affects the partitioning of data in remote databases.

The UPDATE table-name syntax makes an entry in the transaction log, but does not change the database table.

The UPDATE table-name syntax with no OLD and NEW SUBSCRIBE BY expressions must be used in a BEFORE trigger.

The UPDATE table-name syntax with OLD and NEW SUBSCRIBE BY expressions can be used anywhere.

If no OLD and NEW expressions are used, it must be used inside a BEFORE trigger so that it has access to the relevant values. The purpose is to provide a full list of subscribe by values any time the list changes. It is placed in SQL Remote triggers so that the database server can compute the current list of SUBSCRIBE BY values. Both lists are placed in the transaction log.

The Message Agent uses the two lists to make sure that the row moves to any remote database that did not have the row and now needs it. The Message Agent also removes the row from any remote database that has the row and no longer needs it. A remote database that has the row and still needs it is not affected by the UPDATE statement.

The UPDATE table-name syntax allows the old SUBSCRIBE BY list and the new SUBSCRIBE BY list to be explicitly specified, which can make SQL Remote triggers more efficient. In the absence of these lists, the database server computes the old SUBSCRIBE BY list from the publication definition. Since the new SUBSCRIBE BY list is commonly only slightly different from the old SUBSCRIBE BY list, the work to compute the old list may be done twice. By specifying both the old and new lists, this extra work can be avoided.

The OLD and NEW SUBSCRIBE BY syntax is especially useful when many tables are being updated in the same trigger with the same subscribe by expressions. This can dramatically increase performance.

The SUBSCRIBE BY expression is either a value or a subquery.

For publications created using a subquery in a subscription expression, you must write a trigger containing the UPDATE table-name syntax to ensure that the rows are kept in their proper subscriptions.

The UPDATE table-name syntax makes an entry in the transaction log, but does not change the database table.

Privileges

You must be the owner of the table being updated, or have UPDATE privilege on the columns being modified, or have the UPDATE ANY TABLE system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example uses the syntax for the SQL Remote Message Agent to transfer employee Philip Chin (employee 129) from the sales department to the marketing department.

UPDATE GROUPO.Employees
SET DepartmentID = 400
VERIFY ( DepartmentID )
VALUES ( 200 )
WHERE EmployeeID = 129;