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.
NOTE: Partial replace requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
Read more about UPDATE
vs. partial REPLACE
here.
See the examples for more details.
-
/replace
:POST /replace { "table": "<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> }
NOTE: Elasticsearch-like replace requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
-
Partial replace:
POST /<{table | cluster:table}>/_update/<id> { "<field1>": <value1>, ... "<fieldN>": <valueN> }
The
<table name>
can either be just the table name or in the formatcluster:table
. This allows for updates across a specific cluster if needed.NOTE: Partial replace requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
See the examples for more details.
- SQL
- REPLACE SET
- JSON
- Elasticsearch-like
- Elasticsearch-like partial
- Elasticsearch-like partial in cluster
- PHP
- Python
- Python-asyncio
- javascript
- Java
- C#
- Rust
- 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
- Python-asyncio
- javascript
- Java
- C#
- Rust
- 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)