mirror of
https://github.com/lancedb/lancedb.git
synced 2026-10-12 23:02:28 +00:00
Brendan Clement
6a1645116e
Creating, writing, updating, merging and deleting rows, managing tables, branches and read consistency work in the embedded library and on a deployment, and those pages now say so. The SQL forms they mention need a deployment. Where the offerings differ, the section says how: a deployment reuses an existing table without comparing schemas, a merge insert can be refused past a configured number of unindexed rows, space is reclaimed by optimize() locally and by the deployment's compaction and configured cleanup otherwise, renaming depends on the catalog, a deployment clones shallowly only, drops all tables only locally, keeps freshness state per table handle and defaults freshness to its own setting, and refuses to delete a branch main depends on. table_exists works only on a namespace connection. Guarantees and limits span both offerings, so they carry section notes rather than a label: reads, retention, index freshness and compaction differ on a deployment, and the name and job limits are a deployment's.
88 lines
2.6 KiB
Plaintext
88 lines
2.6 KiB
Plaintext
---
|
|
title: "Update rows"
|
|
sidebarTitle: "Update"
|
|
description: "Change values in rows that already exist, by predicate or SQL expression."
|
|
icon: pen
|
|
availability:
|
|
oss: available
|
|
enterprise: available
|
|
summary: >-
|
|
Rows are updated by predicate in the embedded library and on a deployment.
|
|
---
|
|
|
|
These examples use the table set up in
|
|
[Ingesting data](/ingestion), and the connection made there.
|
|
|
|
import {
|
|
PyUpdateOperation as UpdateOperation,
|
|
PyUpdateUsingSql as UpdateUsingSql,
|
|
TsUpdateOperation,
|
|
TsUpdateUsingSql,
|
|
RsUpdateOperation,
|
|
RsUpdateUsingSql,
|
|
} from '/snippets/tables.mdx';
|
|
|
|
<Note>
|
|
In SQL on a deployment, this is [`UPDATE`](/sql/dml/update).
|
|
</Note>
|
|
|
|
## Update rows {#update-rows}
|
|
|
|
Use `update` when you already know which target rows to modify and you do not need to compare against an incoming dataset.
|
|
|
|
<CodeGroup>
|
|
<CodeBlock filename="Python" language="Python" icon="python">
|
|
{UpdateOperation}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
|
|
{TsUpdateOperation}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="Rust" language="Rust" icon="rust">
|
|
{RsUpdateOperation}
|
|
</CodeBlock>
|
|
</CodeGroup>
|
|
|
|
Expected table contents:
|
|
|
|
| id | name | login_count |
|
|
| --- | --- | --- |
|
|
| 1 | Alice | 10 |
|
|
| 2 | Bobby | 20 |
|
|
|
|
<Warning title="Warning">
|
|
Updating nested columns is not yet supported.
|
|
</Warning>
|
|
|
|
## Update rows with SQL expressions {#update-rows-with-sql-expressions}
|
|
|
|
Use `values_sql` when you want to use SQL-like expressions to update rows. This is useful for operations like incrementing a counter, or setting a column value based on another column.
|
|
|
|
<CodeGroup>
|
|
<CodeBlock filename="Python" language="Python" icon="python">
|
|
{UpdateUsingSql}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
|
|
{TsUpdateUsingSql}
|
|
</CodeBlock>
|
|
|
|
<CodeBlock filename="Rust" language="Rust" icon="rust">
|
|
{RsUpdateUsingSql}
|
|
</CodeBlock>
|
|
</CodeGroup>
|
|
|
|
Expected table contents:
|
|
|
|
| id | name | login_count |
|
|
| --- | --- | --- |
|
|
| 1 | Alice | 10 |
|
|
| 2 | Bob | 21 |
|
|
|
|
<Note title="SQL syntax">
|
|
See the [SQL queries](/sql#connecting) page for more information on the supported SQL syntax.
|
|
</Note>
|
|
|
|
When rows are updated, they are moved out of any existing index. The row will still show up in search queries, but the query will not be as fast as it would be if the row was in the index. If you update a large proportion of rows in the embedded library, consider triggering an index rebuild afterwards; a deployment adds updated rows back to its indexes in the background once enough have accumulated.
|