Icon Row Value Constructors

Row value constructors are a special syntax used to aggregate basic expressions into a special row value that can be compared against other row values, or used as a whole in certain DML statments like INSERT and UPDATE. The syntax is as follows:


Each expression in the row value constructor is separated by a comma (,), and a row value constructor requires that at least two expressions be specified in order for the parser to recognize that it is dealing with a row value, as opposed to a simple scalar value enclosed in parentheses. The only exception to this is when a row value constructor is used in an INSERT statement (see below).

Using Row Value Constructors in SELECT, UPDATE, and DELETE Statements
Row value constructors are very useful in SELECT, UPDATE, and DELETE statements for comparing multiple expressions in a single operation. Row value constructors can be used with any comparison operator except for the LIKE/NOT LIKE operators, as the following examples show:

FROM Orders
WHERE (CustNo,OrderNo)=(2156,1020)

(Orders.CustNo,Orders.OrderNo) = (Items.CustNo,Items.OrderNo)

Information Row values are always compared from left-to-right, so all comparison operators work by comparing the first scalar value in the row value, followed by the second, and so on. The IS NULL/IS NOT NULL comparison operators work on an all-or-nothing basis, meaning that the entire row value must be NULL or NOT NULL in order for these operators to return True.

Please see the Comparison Operators topic for more information on the available comparison operators, and the Optimizer topic for more information on how ElevateDB optimizes row value constructors in expressions.

UPDATE statements can also use row value constructors in order to update more than one column at a time in the SET clause. For example:

SET (ShipToState,ShipToCountry)=
   (SELECT State,Country FROM Customer WHERE CustNo=Orders.CustNo)

Using Row Value Constructors in INSERT Statements
Row value constructors can be used in INSERT statements to insert multiple rows in a single statement execution. In order to accomplish this, just separate each row value with a comma (,):

INSERT INTO Orders (OrderNo, ItemNo, QtyOrdered, UnitPrice)
VALUES (1200, 23478, 10, 30.00),
       (1200, 15453, 4, 23.00),
       (1200, 14545, 1, 89.00)

Information You should be careful not to specify too many row values in a single INSERT statement. It is quite possible to exceed the parsing and memory limitations of ElevateDB if you specify hundreds of thousands of row values in a single INSERT statement.