Updates existing rows in a named window or, optionally, inserts new rows into the window if no matching rows are found to update.
|
on_clause |
A stream or window that acts as the trigger for the update. See ON Clause: Trigger Syntax for more information. |
|
when_clause |
A trigger condition. See WHEN Clause for more information. |
|
update_clause |
The destination stream or window. See UPDATE Clause for more information. |
|
set_clause |
The destination columns and associated values. See SET Clause: Window Syntax for more information. |
|
where_clause |
An update condition. See WHERE Clause for more information. |
|
otherwise_insert_clause |
A new row to insert into the destination if the condition in the where_clause isn't met. See OTHERWISE INSERT Clause for more information. |
The ON clause specifies a data stream or named window. Arrival of rows in this stream or window, in combination with the WHEN clause condition (if any), triggers the update or insert process. Sybase CEP Engine processes each row arriving on the stream or window individually, performing an update for each row that meets the condition in the WHEN clause or performing an insert, if specified. This row-at-a-time processing means that rows arriving with the same timestamp can each potentially trigger an update to the same row. Thus the updated window is used when the next row is processed, even if that row has the same timestamp as the previous row.
All other affected CCL statements and clauses treat the window update as a row deletion followed by a row insertion, and respond accordingly. For example, named windows that include an INSERT REMOVED clause treat any updated rows (in their pre-update form) as removed rows. In some cases, this results in two published rows, such as when calculating aggregate values: one for the calculation after the updated row is removed and a second for the calculation after the updated row is inserted.
The clauses within an Update Window statement are processed in the following order, which affects the statement's output:
ON clause.
WHEN clause.
WHERE clause.
SET clause.
OTHERWISE INSERT clause.
UPDATE clause.
ON
OTHERWISE INSERT
SET
UPDATE
WHEN
WHERE
The following example updates the prices of stocks to the most current price. If no stock with the corresponding symbol is found, however, a new row recording the stock symbol and price publishes to the LastTrade window:
ON Trades AS T UPDATE LastTrade AS L SET T.Price AS Price WHERE T.Symbol = L.Symbol OTHERWISE INSERT *;