⪢ Updating documents
You can modify existing data in an RT or PQ table by either updating or replacing it.
UPDATE replaces row-wise attribute values of existing documents with new values. Full-text fields and columnar attributes cannot be updated. If you need to change the content of a full-text field or columnar attributes, use REPLACE.
REPLACE works similarly to INSERT except that if an old document has the same ID as the new document, the old document is marked as deleted before the new document is inserted. Note that the old document does not get physically deleted from the table. The deletion can only happen when chunks are merged in a table, e.g., as a result of an OPTIMIZE.
Both UPDATE
and a partial REPLACE can change the value of a field, but they operate differently:
UPDATE
can only change fields that are neither columnar nor full-text. This process is done in-place, which is typically faster thanREPLACE
.- A partial
REPLACE
can change any field in a document, but it requires that all fields in the table be set to "stored" (though this is the default setting). This is not necessary withUPDATE
.
REPLACE
works similarly to INSERT, but it marks the previous document with the same ID as deleted before inserting a new one.
If you are looking for in-place updates, please see this section.
The syntax of the SQL REPLACE
statement is as follows:
To replace the whole document:
REPLACE INTO table [(column1, column2, ...)]
VALUES (value1, value2, ...)
[, (...)]
To replace only selected fields:
REPLACE INTO table
SET field1=value1[, ..., fieldN=valueN]
WHERE id = <id>
Note, you can filter only by id in this mode.
Read more about UPDATE
vs. partial REPLACE
here.
See the examples for more details.
/replace
:POST /replace { "index": "<table name>", "id": <document id>, "doc": { "<field1>": <value1>, ... "<fieldN>": <valueN> } }
/index
is an alias endpoint and works the same.- Elasticsearch-like endpoint
<table>/_doc/<id>
:PUT/POST /<table name>/_doc/<id> { "<field1>": <value1>, ... "<fieldN>": <valueN> }
- Partial replace:
POST /<table name>/_update/<id> { "<field1>": <value1>, ... "<fieldN>": <valueN> }
See the examples for more details.
- SQL
- REPLACE SET
- JSON
- Elasticsearch-like
- Elasticsearch-like partial
- PHP
- Python
- javascript
- Java
- C#
- TypeScript
- Go
REPLACE INTO products VALUES(1, "document one", 10);
Query OK, 1 row affected (0.00 sec)
REPLACE
is available for real-time and percolate tables. You can't replace data in a plain table.
When you run a REPLACE
, the previous document is not removed, but it's marked as deleted, so the table size grows until chunk merging happens. To force a chunk merge, use the OPTIMIZE statement.
You can replace multiple documents at once. Check bulk adding documents for more information.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
- TypeScript
- Go
REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);
Query OK, 2 rows affected (0.00 sec)