⪢ 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 than REPLACE.
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 with UPDATE.

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 format cluster: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
javascript
Java
C#
TypeScript
Go

📋

REPLACE INTO products VALUES(1, "document one", 10);

REPLACE INTO products SET description='HUAWEI Matebook 15', price=10 WHERE id = 55;

POST /replace
-H "Content-Type: application/x-ndjson" -d '
{
  "table":"products",
  "id":1,
  "doc":
  {
    "title":"product one",
    "price":10
  }
}
'

NOTE: Elasticsearch-like replace requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

PUT /products/_doc/2
{
  "title": "product two",
  "price": 20
}

POST /products/_doc/3
{
  "title": "product three",
  "price": 10
}

NOTE: Partial replace requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

POST /products/_update/55
{
  "doc": {
    "description": "HUAWEI Matebook 15",
    "price": 10
  }
}

POST /cluster_name:products/_update/55
{
  "doc": {
    "description": "HUAWEI Matebook 15",
    "price": 10
  }
}

$index->replaceDocument([
   'title' => 'document one',
    'price' => 10
],1);

indexApi.replace({"table" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}})

res = await indexApi.replace({"table" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}});

docRequest = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
            put("title","document one");
            put("price",10);
}};
docRequest.index("products").id(1L).setDoc(doc);
sqlresult = indexApi.replace(docRequest);

Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "document one");
doc.Add("price", 10);
InsertDocumentRequest docRequest = new InsertDocumentRequest(index: "products", id: 1, doc: doc);
var sqlresult = indexApi.replace(docRequest);

res = await indexApi.replace({
  index: 'test',
  id: 1,
  doc: { content: 'Text 11', name: 'Doc 11', cat: 3 },
});

replaceDoc := map[string]interface{} {"content": "Text 11", "name": "Doc 11", "cat": 3}
replaceRequest := manticoreclient.NewInsertDocumentRequest("test", replaceDoc)
replaceRequest.SetId(1)
res, _, _ := apiClient.IndexAPI.Replace(context.Background()).InsertDocumentRequest(*replaceRequest).Execute()

‹›

Response

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

{
  "_index":"products",
  "_id":1,
  "created":false,
  "result":"updated",
  "status":200
}

{
"_id":2,
"_index":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
    "failed":0,
    "successful":1,
    "total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}

{
"_id":3,
"_index":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
    "failed":0,
    "successful":1,
    "total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}

{
"_index":"products",
"updated":1
}

{
"_index":"products",
"updated":1
}

Array(
    [_index] => products
    [_id] => 1
    [created] => false
    [result] => updated
    [status] => 200
)

{'created': False,
 'found': None,
 'id': 1,
 'table': 'products',
 'result': 'updated'}

{"_index":"products","_id":1,"result":"updated"}

class SuccessResponse {
    index: products
    id: 1
    created: false
    result: updated
    found: null
}

class SuccessResponse {
    index: products
    id: 1
    created: false
    result: updated
    found: null
}

{
    "_index":"test",
    "_id":1,
    "created":false
    "result":"updated"
    "status":200
}

{
    "_index":"test",
    "_id":1,
    "created":false
    "result":"updated"
    "status":200
}

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

POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{ "replace" : { "table" : "products", "id":1, "doc": { "title": "doc one", "tag" : 10 } } }
{ "replace" : { "table" : "products", "id":2, "doc": { "title": "doc two", "tag" : 20 } } }
'

$index->replaceDocuments([
    [
        'id' => 1,
        'title' => 'document one',
        'tag' => 10
    ],
    [
        'id' => 2,
        'title' => 'document one',
        'tag' => 20
    ]
);

indexApi = manticoresearch.IndexApi(client)
docs = [ \
    {"replace": {"table" : "products", "id" : 1, "doc" : {"title" : "document one"}}}, \
    {"replace": {"table" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ]
api_resp = indexApi.bulk('\n'.join(map(json.dumps,docs)))

docs = [
    {"replace": {"table" : "products", "id" : 1, "doc" : {"title" : "document one"}}},
    {"replace": {"table" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ];
res =  await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));

body = "{\"replace\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"document one\"}}}" +"\n"+
    "{\"replace\": {\"index\" : \"products\", \"id\" : 2, \"doc\" : {\"title\" : \"document two\"}}}"+"\n" ;
indexApi.bulk(body);

string body = "{\"replace\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"document one\"}}}" +"\n"+
    "{\"replace\": {\"index\" : \"products\", \"id\" : 2, \"doc\" : {\"title\" : \"document two\"}}}"+"\n" ;
indexApi.Bulk(body);

replaceDocs = [
  {
    replace: {
      index: 'test',
      id: 1,
      doc: { content: 'Text 11', cat: 1, name: 'Doc 11' },
    },
  },
  {
    replace: {
      index: 'test',
      id: 2,
      doc: { content: 'Text 22', cat: 9, name: 'Doc 22' },
    },
  },
];

res = await indexApi.bulk(
  replaceDocs.map((e) => JSON.stringify(e)).join("\n")
);

body := "{\"replace\": {\"index\": \"test\", \"id\": 1, \"doc\": {\"content\": \"Text 11\", \"name\": \"Doc 11\", \"cat\": 1 }}}" + "\n" +
    "{\"replace\": {\"index\": \"test\", \"id\": 2, \"doc\": {\"content\": \"Text 22\", \"name\": \"Doc 22\", \"cat\": 9 }}}" +"\n";
res, _, _ := apiClient.IndexAPI.Bulk(context.Background()).Body(body).Execute()

‹›

Response

Query OK, 2 rows affected (0.00 sec)

{
  "items":
  [
    {
      "replace":
      {
        "_index":"products",
        "_id":1,
        "created":false,
        "result":"updated",
        "status":200
      }
    },
    {
      "replace":
      {
        "_index":"products",
        "_id":2,
        "created":false,
        "result":"updated",
        "status":200
      }
    }
  ],
  "errors":false
}

Array(
    [items] =>
    Array(
        Array(
            [_index] => products
            [_id] => 2
            [created] => false
            [result] => updated
            [status] => 200
        )
        Array(
            [_index] => products
            [_id] => 2
            [created] => false
            [result] => updated
            [status] => 200
        )
    )
    [errors => false
)

{'error': None,
 'items': [{u'replace': {u'_id': 1,
                         u'_index': u'products',
                         u'created': False,
                         u'result': u'updated',
                         u'status': 200}},
           {u'replace': {u'_id': 2,
                         u'_index': u'products',
                         u'created': False,
                         u'result': u'updated',
                         u'status': 200}}]}

{"items":[{"replace":{"_index":"products","_id":1,"created":false,"result":"updated","status":200}},{"replace":{"_index":"products","_id":2,"created":false,"result":"updated","status":200}}],"errors":false}

class BulkResponse {
    items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
    error: null
    additionalProperties: {errors=false}
}

class BulkResponse {
    items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
    error: null
    additionalProperties: {errors=false}
}

{
  "items":
  [
    {
      "replace":
      {
        "_index":"test",
        "_id":1,
        "created":false,
        "result":"updated",
        "status":200
      }
    },
    {
      "replace":
      {
        "_index":"test",
        "_id":2,
        "created":false,
        "result":"updated",
        "status":200
      }
    }
  ],
  "errors":false
}

{
  "items":
  [
    {
      "replace":
      {
        "_index":"test",
        "_id":1,
        "created":false,
        "result":"updated",
        "status":200
      }
    },
    {
      "replace":
      {
        "_index":"test",
        "_id":2,
        "created":false,
        "result":"updated",
        "status":200
      }
    }
  ],
  "errors":false
}

⪢ Updating documents

REPLACE vs UPDATE

UPDATE vs partial REPLACE

REPLACE

SQL REPLACE

JSON REPLACE

Bulk replace