Defines a list of values and column associations, which generates a new row and publishes it to the destination named window specified in an Update Window statemen if a trigger occurs but no current rows in the window match the statement's update condition.
This optional clause of an Update Window statement takes effect only when a row arrives in the triggering stream or window specified in the ON clause and meets the criteria of the WHEN condition if one is set but no rows in the statement's destination window, defined in the UPDATE clause, match the update condition set by the WHERE clause. In other words, when a trigger occurs but the destination window doesn't contain any rows that can be updated.
The OTHERWISE INSERT clause contains an insert list of one or more items, separated by commas. Each expression in the insert list can contain literals, column references from the triggering stream or window specified in the Update Window statement's ON clause, operators, scalar and miscellaneous functions, and parentheses, or can contain the "select all" asterisk (*) character, which is equivalent to a list of all column values from the triggering stream or window in the ON clause, listed in order from left to right.
Items in the insert list that are not an asterisk can include an AS subclause, indicating a column in the destination window's schema to which the results of the expression are published. You must use the AS subclause either for all of the non-asterisk items or for none of the items in the insert list.
If the insert list includes AS subclauses, the output column reference associated with each expression must uniquely match a column name in the destination's named window schema, and the expression must evaluate to the column's data type. In the absence of an AS subclause, items are published in order from left to right.
The insert list must contain a value for every column in the destination window's schema.
OTHERWISE INSERT clause expressions cannot include column values from the named window specified in the UPDATE clause, since OTHERWISE INSERT executes only when no corresponding value is found in this named window.
Update Window Statement
ON
UPDATE
WHEN
WHERE
ON ShippedOrders AS S WHEN Code = "Ship Notice" UPDATE Orders AS O SET TRUE AS Shipped, S.ShippedTime AS ShippedTime WHERE S.OrderID = O.OrderID OTHERWISE INSERT S.OrderID AS OrderID, TRUE AS Shipped, S.ShippedTime AS ShippedTime;