USER MANUALS

UPDATE Statement

The UPDATE statement allows modifying the value of certain attributes in all tuples of a view that verify a certain condition, directly updating the underlying data source.

Syntax of the UPDATE statement
UPDATE <view identifier>
    SET ( <field name> [, <field name> ]* ) = ( <value> [, <value> ]* )
    [ WHERE <condition> ]
    [ RETURNING <returning options>]
    [ CONTEXT ( <context information> [, <context information> ]* ) ]
    [ TRACE ]

UPDATE <view identifier>
    SET <field name> = <complex value> [, <field name> = <complex value> ]*
    [ WHERE <condition> ]
    [ RETURNING <returning options>]
    [ CONTEXT ( <context information> [, <context information> ]* ) ]
    [ TRACE ]

UPDATE <view identifier>
     SET ( <field name> [, <field name> ]* ) = ( <complex select> )
     [ WHERE <condition> ]
     [ RETURNING <returning options>]
     [ CONTEXT ( <context information> [, <context information> ]* ) ]
     [ TRACE ]

<field name> ::= <identifier>[.<identifier>]

<value> ::=
    NULL
  | <number>
  | <boolean>
  | <literal>
  | <field name>
  | <function>

<complex value> ::=
    <value>
  | <complex select>

<condition> ::=
    <condition> AND <condition>
  | <condition> OR <condition>
  | NOT <condition>
  | ( <condition> )
  | <value> <binary operator> <value> [ , <value> ]*
  | <value> <unary operator>

<returning options> ::=
  ALL GENERATED KEYS
  | ( <number> [, <number> ]* )
  | <field name> [, <field name> ]*

<view identifier> ::= (see Basic elements of VQL statements)

The section Return Modified Rows explains how the RETURNING clause works.

Examples:

Example #1

The following statement updates the tuples of the internet_inc view whose value of iinc_id is 6, setting to 10 its value for the attributes specific_field1 and specific_field2:

UPDATE internet_inc
SET (specific_field1, specific_field2) = ('10', '10')
WHERE iinc_id = 6

As a result of executing this statement, the corresponding tuples in the source database will be altered in the table associated with the

internet_inc view.

Example #2

It is also possible to use the alternative syntax:

UPDATE internet_inc
SET specific_field1 = '10', specific_field2 = '10'
WHERE iinc_id = 6

Example #3

For JDBC data sources, it is also possible to update the tuples of the internet_inc view with the values taken from a subquery which returns only one row when the full update query is delegable to a unique JDBC data source.

UPDATE internet_inc SET (specific_field1, specific_field2) =
  (SELECT B.rating, B.comments FROM internet_inc_rating B
        WHERE B.id = iinc_id and iinc_id = 6)

Example #4

The subquery can be applied to a single view field as well:

UPDATE internet_inc SET
  specific_field1 = (SELECT B.rating FROM internet_inc_rating B
        WHERE B.id = iinc_id and iinc_id = 6),
  specific_field2 = 'literal_value'
WHERE
  (iinc_id, summary) in (SELECT B.iinc_id, B.summary FROM internet_inc_rating B
        WHERE B.summary = 'value_to_update')
Add feedback