⪢ Updating documents

REPLACE vs UPDATE

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.

UPDATE vs partial REPLACE

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

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.

SQL REPLACE

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.

JSON REPLACE

  • /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);
‹›
Response
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.

Bulk replace

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);
‹›
Response
Query OK, 2 rows affected (0.00 sec)