UPDATE

Documentation

VoltDB Home » Documentation » Using VoltDB
< previoustable of contentsnext >

UPDATE

UPDATE — Updates the values within the specified columns and rows of the database.

Synopsis

UPDATE table-name SET column-name = value-expression [, ...]
[WHERE [NOT] boolean-expression [ {AND | OR} [NOT] boolean-expression]...]

Description

The UPDATE statement changes the values of columns within the specified records. The following limitations are important to note when using the UPDATE statement with VoltDB:

  • VoltDB supports the following arithmetic operators in expressions: addition (+), subtraction (-), multiplication (*), and division (*).

  • The WHERE expression supports the boolean operators: equals (=), not equals (!= or <>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), IS NULL, AND, OR, and NOT. Note, however, although OR is supported syntactically, VoltDB does not optimize these operations and use of OR may impact the performance of your queries.

  • You can use subqueries in place of value expressions within the SET and WHERE clauses of the UPDATE statement, with the following provisions:

    • See the description of subqueries in the SELECT statement for general rules concerning the construction of subqueries.

    • In a multi-partition procedure, subqueries of the UPDATE statement can only reference replicated tables.

    • In single-partitioned procedures, the subquery can reference both partitioned and replicated tables.

    • For ad hoc UPDATE statements, the same rules apply except the SQL statement itself determines whether VoltDB executes it as a single-partitoned or multi-partitioned procedure. Statements that modify a partitioned table based on a specific value of the partitioning column are executed as single-partitioned procedures. All other statements are multi-partitioned.

Examples

The following example changes the ADDRESS column of the EMPLOYEE record with an employee ID of 145303:

UPDATE employee 
    SET address = '49 Lavender Sweep' 
    WHERE employee_id = 145303;

The following example increases the starting price by 25% for all ITEM records with a category ID of 7:

UPDATE item SET startprice = startprice * 1.25 WHERE categoryid = 7;
< previoustable of contentsnext >