Download HyperSQL User Guide

Data Access and Change
<update source> ::= <value expression> | <contextually typed value specification>
Specify a list of assignments. This is used in UPDATE, MERGE and SET statements to assign values to a scalar or
row target.
Apart from setting a whole target to a value, a SET statement can set individual elements of an array to new values.
The last example below shows this form of assignment to the array in the column named B.
In the examples given below, UPDATE statements with single and multiple assignments are shown. Note in the third
example, a SELECT statement is used to provide the update values for columns A and C, while the update value for
column B is given separately. The SELECT statement must return exactly one row . In this example the SELECT
statement refers to the existing value for column C in its search condition.
UPDATE
UPDATE
UPDATE
UPDATE
T
T
T
T
SET
SET
SET
SET
A =
(A,
(A,
A =
5 WHERE ...
B) = (1, NULL) WHERE ...
C) = (SELECT X, Y FROM U WHERE Z = C), B = 10 WHERE ...
3, B[3] = 'warm'
Merge Statement
MERGE INTO
merge statement
<merge statement> ::= MERGE INTO <target table> [ [ AS ] <merge correlation
name> ] USING <table reference> ON <search condition> <merge operation specification>
<merge correlation name> ::= <correlation name>
<merge operation specification> ::= <merge when clause>...
<merge when clause> ::= <merge when matched clause> | <merge when not matched
clause>
<merge when matched clause> ::= WHEN MATCHED THEN <merge update specification>
<merge when not matched clause> ::= WHEN NOT MATCHED THEN <merge insert specification>
<merge update specification> ::= UPDATE SET <set clause list>
<merge insert specification> ::= INSERT [ <left paren> <insert column list>
<right paren> ] [ <override clause> ] VALUES <merge insert value list>
<merge insert value list> ::= <left paren> <merge insert value element> [ { <comma> <merge insert value element> }... ] <right paren>
<merge insert value element> ::= <value expression> | <contextually typed value
specification>
Update rows, or insert new rows into the <target table>. The MERGE statement uses a second table, specified
by <table reference>, to determine the rows to be updated or inserted. It is possible to use the statement only
to update rows or to insert rows, but usually both update and insert are specified.
The <search condition> matches each row of the <table reference> with each row of the <target
table>. If the two rows match then the UPDATE clause is used to update the matching row of the target table. Those
128