UPDATE

Applies to VAST Cluster 5.5.

Changes column values in the rows that match a condition.

Syntax

UPDATE "<bucket>/<schema>".<table>
SET <column> = <expression> [, <column> = <expression> ...]
WHERE <condition>

Parameters and rules

• The statement returns one row with one column, Count: the number of rows changed.
• The new values can come only from the row itself. UPDATE ... FROM <other table> needs a join, and fails.
• On a sorted table, you can't change a sort column to a new value: the statement fails with Invalid Argument (updates and deletes). Other columns can be updated.
• On a vector-indexed table, you can't update the vector column. Delete the row and insert it again (vector search SQL).
• The Query Engine can't update a partitioned table (not supported).

What happens on the cluster

The Query Engine finds the rows that match WHERE, computes the new values, and writes them on the cluster. The same rules decide whether the WHERE conditions are evaluated in VAST DataBase or in the Query Engine. Only the count comes back. For what an update does to the stored data, see write path.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, and creates the schema docs_sql_update with three rows:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_update"'

def connect(autocommit=True):
    return dbapi.connect(
        driver=adbc_driver_vastdb.get_driver_path(),
        db_kwargs={
            "vast.db.endpoint": os.environ["QE_ENDPOINT"],
            "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
            "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
        },
        autocommit=autocommit,
    )

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

conn = connect()
TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, city VARCHAR, temperature DOUBLE, tags INTEGER[])")
run(f"INSERT INTO {TABLE} VALUES (1, 'Lisbon', 21.5, [1]), (2, 'Oslo', 4.0, []), (3, 'Cairo', 30.2, [2, 3])")

[]
[]
[(3,)]

Set a constant, compute from the row, and append to a list:

run(f"UPDATE {TABLE} SET city = 'Lisboa' WHERE id = 1")
run(f"UPDATE {TABLE} SET temperature = temperature + 1 WHERE temperature < 25")
run(f"UPDATE {TABLE} SET tags = list_append(tags, 9) WHERE city LIKE 'C%'")
run(f"SELECT id, city, temperature, tags FROM {TABLE} ORDER BY id LIMIT 10")

[(1,)]
[(2,)]
[(1,)]
[(1, 'Lisboa', 22.5, [1]), (2, 'Oslo', 5.0, []), (3, 'Cairo', 30.2, [2, 3, 9])]

Taking values from another table fails:

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.names (id BIGINT, city VARCHAR)")
run(f"UPDATE {TABLE} SET city = n.city FROM {SCHEMA}.names n WHERE readings.id = n.id")

[]
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP TABLE IF EXISTS {SCHEMA}.names")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]
[]

Errors

Trino and Spark

Trino and Spark update with their own UPDATE statements, and Trino also has MERGE; their WHERE can use anything the engine evaluates. See updates and deletes.

See also: DELETE and TRUNCATE, updates and deletes.