Referencing fields in row variables

A row field value can be referenced wherever a value of the field's data type is permitted. The following contexts are supported for referencing row fields:

• Wherever a value of the field's data type is permitted including, but not limited to:
  • As the source of an assignment (SET statement)
  • As the target of an assignment (SET statement)
  • As the target of SELECT INTO, VALUES INTO, or FETCH INTO statement.

To reference the values of fields in a row variable a single-dot notation is used. Field values are associated with variables as follows:

<row-variable-name>.<field-name>

The following is an example of how to access the field id of variable employee:

employee.id

Examples of supported references to row variable field values follow.

The following is an example that shows how to assign a literal value to a field in row variable v1:

-- Literal assignment to a row variable field
SET v1.c1 = 5;

The following example shows how to assign literal and expression values to multiple row variable fields:

-- Literal assignment to fields of row variable
SET (emp.id, emp.name) = (v1.c1 + 1, 'James');

The following example shows how to reference field values in an INSERT statement:

-- Field references in an INSERT statement
INSERT INTO employee 
VALUES(v1.c1, 'Beth'), 
      (emp.id, emp.name); 

The following example shows how to reference field values in an UPDATE statement:

-- Field references in an UPDATE statement
UPDATE employee 
SET name = 'Susan' 
WHERE id = v1.c1;

The following example shows how to reference field values in a SELECT INTO statement:

-- Field references in a SELECT INTO statement
SELECT employee.firstname INTO v2.c1 
FROM employee 
WHERE name=emp.name;