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:

(<Expression>,<Expression>[,<Expression>])

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:

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

SELECT *
FROM Orders INNER JOIN Items ON
(Orders.CustNo,Orders.OrderNo) = (Items.CustNo,Items.OrderNo)

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:

UPDATE Orders
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)