Update rows reference

This Astra DB Serverless feature is currently in public preview. Development is ongoing, and the features and functionality are subject to change. Astra DB Serverless, and the use of such, is subject to the DataStax Preview Terms.

The Data API tables commands are available through HTTP and the clients.

If you use a client, tables commands are available only in client versions 2.0-preview or later. For more information, see Data API client upgrade guide.

A row represents a single record of data in a table in an Astra DB Serverless database.

You use the Table class to work with rows through the Data API clients. For instructions to get a Table object, see Work with tables.

For general information about working with rows, including common operations and operators, see Work with rows.

For more information about the Data API and clients, see Get started with the Data API.

Update a row

Modify the values in a row in a table by setting new non-null values or removing existing values (changing them to null).

The outcome of this command depends on the update prescription:

  • If you $set at least one non-null value, the following can occur:

    • If there is no match for the given primary key, then the Data API creates a new row with the given $set values and primary key values. Any omitted or $unset columns are set to null.

    • If there is a match, the matching row is modified.

  • If you only change values to null, the following can occur:

    • If there is no match for the given primary key, then the Data API makes no changes and does not create a new row.

    • If there is a match, the matching row is modified.

      A rare edge case, related to underlying Apache Cassandra® functionality, can cause rows to disappear altogether when all of its columns are set to null.

      This happens if the row was previously created from an update operation that had no pre-existing row to modify.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

Update a column value for a certain row (creating the row if it does not exist yet):

my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"winner": "Winona"}},
)

Unset two values (set them to null) for one row in a table:

my_table.update_one(
    {"match_id": "challenge6", "round": 2},
    update={"$unset": {"winner": None, "score": None}},
)

Parameters:

Name Type Summary

filter

dict

Describes the row to update (or upsert) by its primary key values.

You cannot filter on non-primary keys.

Only the the $eq operator is allowed.

update

dict

The update prescription to apply to the row. Use the $set and $unset operators to change or remove values from specified columns. For example:

  • { "$set": { "COLUMN": "NEW_VALUE" } }

  • { "$unset": { "COLUMN": "" } }

The target value of $unset doesn’t matter; use of the $unset operator changes the given columns to null or the equivalent empty form, such as [] or {} for map, list, and set types.

You can’t use updateOne to modify primary key values. If you need to modify a row’s primary key, you must delete the row and then insert a new row with the desired primary key values.

general_method_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

If there is no matching row for the given primary key, the Data API can create a new row with the values specified in filter and update, setting any omitted columns to null. However, if update includes only null or empty ("") values, the Data API does not create a new row. For more information, see the explanation of the update_one result.

Full example script
from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.data_types import (
    DataAPISet,
    DataAPITimestamp,
    DataAPIVector,
)
from astrapy.ids import UUID

insert_result = my_table.insert_many(
    [
        {
            "match_id": "fight4",
            "round": 1,
            "winner": "Victor",
            "score": 18,
            "when": DataAPITimestamp.from_string(
                "2024-11-28T11:30:00Z",
            ),
            "fighters": DataAPISet([
                UUID("0193539a-2770-8c09-a32a-111111111111"),
                UUID('019353e3-00b4-83f9-a127-222222222222'),
            ]),
            "m_vector": DataAPIVector([0.4, -0.6, 0.2]),
        },
        {"match_id": "challenge6", "round": 2, "winner": "Erick"},
    ],
)

from astrapy.data_types import DataAPISet

# Set a new value for a column
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"winner": "Winona"}},
)

# Set a new value for a column while unsetting another colum
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"winner": None, "score": 24}},
)

# Set a 'set' column to empty
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": DataAPISet()}},
)

# Set a 'set' column to empty using None
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": None}},
)

# Set a 'set' column to empty using a regular (empty) set
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": set()}},
)

# Set a 'set' column to empty using $unset
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$unset": {"fighters": None}},
)

# A non-existing primary key creates a new row
my_table.update_one(
    {"match_id": "bar_fight", "round": 4},
    update={"$set": {"score": 8, "winner": "Jack"}},
)

# Delete column values for a row (they will now read as None)
my_table.update_one(
    {"match_id": "challenge6", "round": 2},
    update={"$unset": {"winner": None, "score": None}},
)

Example:

from astrapy.data_types import DataAPISet

# Set a new value for a column
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"winner": "Winona"}},
)

# Set a new value for a column while unsetting another colum
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"winner": None, "score": 24}},
)

# Set a 'set' column to empty
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": DataAPISet()}},
)

# Set a 'set' column to empty using None
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": None}},
)

# Set a 'set' column to empty using a regular (empty) set
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$set": {"fighters": set()}},
)

# Set a 'set' column to empty using $unset
my_table.update_one(
    {"match_id": "fight4", "round": 1},
    update={"$unset": {"fighters": None}},
)

# A non-existing primary key creates a new row
my_table.update_one(
    {"match_id": "bar_fight", "round": 4},
    update={"$set": {"score": 8, "winner": "Jack"}},
)

# Delete column values for a row (they will now read as None)
my_table.update_one(
    {"match_id": "challenge6", "round": 2},
    update={"$unset": {"winner": None, "score": None}},
)

For more information, see the Client reference.

Update a column value for a certain row (creating the row if it does not exist yet):

await table.updateOne({ matchId: 'fight4', round: 1 }, { $set: { winner: 'Winona' } });

Unset two values (set them to null) for one row in a table:

await table.updateOne({ matchId: 'challenge6', round: 2 }, { $unset: { winner: '', score: '' } });
Upsert example
// No upsert will occur here since only nulls are being set
// (this is equivalent to { $unset: { name: '' } })
await table.updateOne({ key: '123' }, { $set: { name: null } });

// An upsert will occur here since at least one non-null value is being set
await table.updateOne({ key: '123' }, { $set: { name: 'Eleanor', age: null } });

$set`-ing a row to null is equivalent to $unset-ing it. The following example would be the exact same using `$unset`s.

Delete example
// Upserts row { key: '123', name: 'Michael', age: 3 } into the table
await table.updateOne({ key: '123' }, { $set: { name: 'Michael', age: 3 } });

// Sets row to { key: '123', name: 'Michael', age: null }
await table.updateOne({ key: '123' }, { $set: { age: null } });

// Deletes row from the table as all non-primary keys are set to null
await table.updateOne({ key: '123' }, { $set: { name: null } });

Updates may perform either $set or $unset operations on the row.

Parameters:

Name Type Summary

filter

TableFilter

Describes the row to update (or upsert) by its primary key values.

You cannot filter on non-primary keys.

Only the the $eq operator is allowed.

update

TableUpdateFilter

The update prescription to apply to the row. Use the $set and $unset operators to change or remove values from specified columns.

The target value of $unset doesn’t matter; use of the $unset operator changes the given columns to null or the equivalent empty form, such as [] or {} for map, list, and set types.

You can’t use updateOne to modify primary key values. If you need to modify a row’s primary key, you must delete the row and then insert a new row with the desired primary key values.

timeout?

WithTimeout

The client-side timeout for this operation.

Returns:

Promise<void> - A promise that resolves when the operation is complete.

Why void?

The updateOne operation, as returned from the Data API, is always { matchedCount: 1, modifiedCount: 1 }, regardless of how many things are matched or modified, and regardless of whether a row was upserted. Therefore, void is used.

If there is no matching row for the given primary key, the Data API can create a new row with the values specified in filter and update, setting any omitted columns to null. However, if update includes only null or empty ("") values, the Data API does not create a new row. For more information, see the explanation of the updateOne result.

Example:

Full script
import { CreateTableDefinition, DataAPIClient, InferTablePrimaryKey, InferTableSchema, timestamp, uuid, vector } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax, and then infer the type.
// For information about table typing and definitions, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

type TableSchema = InferTableSchema<typeof TableDefinition>;

(async function () {
  // Create a table with the given TableSchema type if a 'games' table doesn't already exist
  const table = await db.createTable<TableSchema>('games', { definition: TableDefinition, ifNotExists: true });

  // Insert some rows in an unordered fashion.
  await table.insertMany([
    { matchId: 'fight4', round: 1, winner: 'Victor' },
    { matchId: 'fight5', round: 1, winner: 'Adam' },
    { matchId: 'fight5', round: 2, winner: 'Betta' },
    { matchId: 'fight5', round: 3, winner: 'Caio' },
    { matchId: 'fight7', round: 1, winner: 'Joy' },
    { matchId: 'fight7', round: 2, winner: 'Kevin' },
    { matchId: 'fight7', round: 3, winner: 'Lauretta' },
  ]);

// Use updateOne to update individual values in a row.
// updateOne can insert a new row, depending on the specified update operation.

  // Set a new non-null value for a column
  await table.updateOne(
    { matchId: 'fight4', round: 1 },
    { $set: { winner: 'Winona' } },
  );

  // Set a new non-null value for a column
  // and nullify the existing value in another column
  await table.updateOne(
    { matchId: 'fight4', round: 1 },
    { $set: { winner: null, score: 24 } },
  );

  // Set a 'set' type column to empty
  await table.updateOne(
    { matchId: 'fight4', round: 1 },
    { $set: { fighters: new Set() } },
  );

  // Use unset to change a 'set' type column to empty
  await table.updateOne(
    { matchId: 'fight4', round: 1 },
    { $unset: { fighters: '' } },
  );

  // If the given primary key doesn't match any existing row,
  // and you $set at least one non-null value,
  // then updateOne creates a new row with the given primary key and non-null values.
  // All unspecified columns are null.
  await table.updateOne(
    { matchId: 'bar_fight', round: 4 },
    { $set: { score: 8, winner: 'Jack' } },
  );

  // You can use set and unset to change all non-primary key columns to null.
  // You can't change primary key values with updateOne.
  await table.updateOne(
    { matchId: 'challenge6', round: 2 },
    { $unset: { winner: '', score: '' } },
  );

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Set a new non-null value for a column
await table.updateOne(
  { matchId: 'fight4', round: 1 },
  { $set: { winner: 'Winona' } },
);

// Set a new non-null value for a column
// and nullify the existing value in another column
await table.updateOne(
  { matchId: 'fight4', round: 1 },
  { $set: { winner: null, score: 24 } },
);

// Set a 'set' type column to empty
await table.updateOne(
  { matchId: 'fight4', round: 1 },
  { $set: { fighters: new Set() } },
);

// Use unset to change a 'set' type column to empty
await table.updateOne(
  { matchId: 'fight4', round: 1 },
  { $unset: { fighters: '' } },
);

// If the given primary key doesn't match any existing row,
// and you $set at least one non-null value,
// then updateOne creates a new row with the given primary key and non-null values.
// All unspecified columns are null.
await table.updateOne(
  { matchId: 'bar_fight', round: 4 },
  { $set: { score: 8, winner: 'Jack' } },
);

// You can use set and unset to change all non-primary key columns to null.
// You can't change primary key values with updateOne.
await table.updateOne(
  { matchId: 'challenge6', round: 2 },
  { $unset: { winner: '', score: '' } },
);

For more information, see the Client reference.

Update a column value for a certain row (creating the row if it does not exist yet):

Filter filter = and(
 eq("match_id", "fight4"),
 eq("round", 1)
);

TableUpdateOperation operation =
  new TableUpdateOperation()
   .set("winner", "Winona");

TableUpdateOneOptions options =
  new TableUpdateOneOptions()
   .timeout(1000);

myTable.updateOne(filter, operation, options);

Unset two values (set them to null) for one row in a table:

Filter filter2 = and(
  eq("challenge6", "fight4"),
  eq("round", 2)
);
tableRow.updateOne(filter2, new TableUpdateOperation()
  .unset("winner", "score"));

Parameters:

Name Type Summary

filter

Filter

Describes the row to update (or upsert) by its primary key values.

You cannot filter on non-primary keys.

Only the the $eq operator is allowed.

Filters can be instantiated with its constructor and specialized with method where(..) or leverage the class Filters

operation

TableUpdateOperation

A wrapper for the different options and specialization of this search.

options

TableUpdateOneOptions

Operations to be applied to the update operation. It could be set or unset operations.

Method Summary

set(String column, Object value)

Set a column to a new value.

unset(String column, Object value)

Unset a column, setting it to a provided empty value, "", null, Set.of(), etc.

unset(String…​ column)

Unset a list of columns.

Name Type Summary

timeout

long or Duration

Overrides the client-side timeout for this operation. If not provided, the Table defaults apply.

Returns:

If there is no matching row for the given primary key, the Data API can create a new row with the values specified in filter and update, setting any omitted columns to null. However, if update includes only null or empty ("") values, the Data API does not create a new row. For more information, see the explanation of the updateOne result.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.collections.commands.Update;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.core.query.Sort;
import com.datastax.astra.client.core.vector.DataAPIVector;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.TableUpdateOperation;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.commands.options.TableUpdateOneOptions;
import com.datastax.astra.client.tables.commands.results.TableUpdateResult;
import com.datastax.astra.client.tables.definition.rows.Row;
import com.datastax.astra.client.tables.mapping.Column;
import lombok.Data;

import java.util.Optional;
import java.util.Set;

import static com.datastax.astra.client.core.query.Filters.and;
import static com.datastax.astra.client.core.query.Filters.eq;
import static com.datastax.astra.client.core.query.Filters.gt;

public class UpdateOne {
 public static void main(String[] args) {
  Database db = new DataAPIClient("token").getDatabase("endpoint");

  Table<Row> tableRow = db.getTable("games");

  // Update
  Filter filter = and(
    eq("match_id", "fight4"),
    eq("round", 1));
  new TableUpdateOneOptions()
    .timeout(1000);
  tableRow.updateOne(filter, new TableUpdateOperation()
    .set("winner", "Winona"));

  // Unset 2 columns
  tableRow.updateOne(filter, new TableUpdateOperation()
    .set("winner", "Winona"));

  // Set a new value for a column
  tableRow.updateOne(and(
    eq("match_id", "fight4"),
    eq("round", 1)),
  new TableUpdateOperation().set("winner", "Winona"));

  // Set a new value for a column while unsetting another colum
  tableRow.updateOne(and(
    eq("match_id", "fight4"),
    eq("round", 1)),
  new TableUpdateOperation()
    .set("score", 24)
    .unset("winner")
    .unset("fighters", Set.of()));
 }

}

Update values in one row in a table:

curl -sS -L -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "updateOne": {
    "filter": {
      "PRIMARY_KEY_COLUMN": "PRIMARY_KEY_VALUE"
    },
    "update": {
      "$set": {
        "COLUMN_NAME": "NEW_VALUE"
      },
      "$unset": {
        "COLUMN_NAME": ""
      }
    }
  }
}' | jq

Parameters:

Name Type Summary

updateOne

command

Data API command to update one row in a table.

filter

object

Describes the row to update (or upsert) by its primary key values.

You cannot filter on non-primary keys.

Only the the $eq operator is allowed.

update

object

The update prescription to apply to the row. Use the $set and $unset operators to change or remove values from specified columns. For example:

  • { "$set": { "COLUMN": "NEW_VALUE" } }

  • { "$unset": { "COLUMN": "" } }

The target value of $unset doesn’t matter; use of the $unset operator changes the given columns to null or the equivalent empty form, such as [] or {} for map, list, and set types.

You can’t use updateOne to modify primary key values. If you need to modify a row’s primary key, you must delete the row and then insert a new row with the desired primary key values.

Returns:

A well-formed response returns "matchedCount": 1, "modifiedCount": 1, and "upsertCount": 0 regardless of the actual outcome.

Example response
{
  "status": {
    "matchedCount": 1,
    "modifiedCount": 1,
    "upsertCount": 0
  }
}

If there is no matching row for the given primary key, the Data API can create a new row with the values specified in filter and update, setting any omitted columns to null. However, if update includes only null or empty ("") values, the Data API does not create a new row. For more information, see the explanation of the updateOne result.

Example:

curl -sS -L -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/default_keyspace/students" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "updateOne": {
    "filter": {
      "email": "tal@example.com"
    },
    "update": {
	    "$set": {
	      "graduation_year": 2025,
	      "major": "biology"
	    },
	    "$unset": {
	      "minor": ""
	    }
	  }
  }
}' | jq

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com