Update a row

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.

Updates a single row in a table.

If the row does not already exist and the update includes at least one non-null or non-empty value, creates a new row.

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

Method signature

Python
TypeScript
Java
curl

table.update_one(
  filter: Dict[str, Any],
  update: Dict[str, Any],
  *,
  general_method_timeout_ms: int,
  request_timeout_ms: int,
  timeout_ms: int,
) -> None

table.updateOne(
  filter: TableFilter<WSchema>,
  update: TableUpdateFilter<WSchema>,
  timeout?: WithTimeout<'generalMethodTimeoutMs'>
): Promise<void>

void updateOne(
  Filter filter,
  TableUpdateOperation update
)

void updateOne(
  Filter filter,
  TableUpdateOperation update,
  TableUpdateOneOptions options
)

curl -sS -L -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/TABLE_NAME" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "updateOne": {
    "filter": FILTER,
    "update": UPDATE
  }
}'

Result

Python
TypeScript
Java
curl

Updates the specified row.

If no row matches the specified primary key and the update includes at least one non-null or non-empty value, then a new row is created with the values specified $set values and primary key values. Any omitted or $unset columns are set to null in the new row.

Does not return anything.

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.

Updates the specified row.

Returns Promise<void>.

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.

Updates the specified row.

Does not return anything.

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.

Updates the specified row.

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.

Always returns a status.matchedCount of 1, a status.modifiedCount of 1, and a status.upsertCount of 0, regardless of the outcome.

Example response:

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

Parameters

Python
TypeScript
Java
curl

Name Type Summary

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.

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.

Name Type Summary

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.

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.

Name Type Summary

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.

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.

TableUpdateOperation

Method Summary

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.

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.

TableUpdateOneOptions

Name Type Summary

Name	Type	Summary
`timeout`	`long` or `Duration`	Overrides the client-side timeout for this operation. If not provided, the `Table` defaults apply.

timeout

long or Duration

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

Name Type Summary

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.

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.

Examples

The following examples demonstrate how to update a row in a table.

Python
TypeScript
Java
curl

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}},
)

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}},
)

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.

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: '' } },
);

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"));

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

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

Client reference

Python
TypeScript
Java
curl

For more information, see the client reference.

Client reference documentation is not applicable for HTTP.

Update a row

Method signature

Result

Parameters

Examples

Client reference

Was this helpful?

Give Feedback