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 tonull
. -
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 |
---|---|---|
|
|
Describes the row to update (or upsert) by its primary key values. You cannot filter on non-primary keys. Only the the |
|
|
The update prescription to apply to the row.
Use the
The target value of You can’t use |
|
|
A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as |
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 |
---|---|---|
|
|
Describes the row to update (or upsert) by its primary key values. You cannot filter on non-primary keys. Only the the |
|
|
The update prescription to apply to the row.
Use the The target value of You can’t use |
|
|
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 |
---|---|---|
|
||
|
A wrapper for the different options and specialization of this search. |
|
|
Operations to be applied to the update operation. It could be |
Method | Summary |
---|---|
|
Set a column to a new value. |
|
Unset a column, setting it to a provided empty value, |
|
Unset a list of columns. |
Name | Type | Summary |
---|---|---|
|
|
Overrides the client-side timeout for this operation. If not provided, the |
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 |
---|---|---|
|
|
Data API command to update one row in a table. |
|
|
Describes the row to update (or upsert) by its primary key values. You cannot filter on non-primary keys. Only the the |
|
|
The update prescription to apply to the row.
Use the
The target value of You can’t use |
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